I have made a few userforms. One of them is a "starter". In this I have a few buttons and each button opens a another useform. When I close another userform the starter one opens. The problem is that when I click an button for the second time that userformer opens with old data and then freezes. How do I prevent the "freezing"?
View 9 RepliesI have an Excel worksheet that when a user enters something into cell A1 I want to clear cells (b2:b20). If I use an object, I can attach a macro to it, but I don't want to use an object. I want to use an Excel cell and then when the user types into it, I want to perform an action.
View 9 Replies View RelatedI have a multipage form that I'm using to create a wizard that accepts data from the active workbook via a refedit box, however, when I try to execute the entire form freezes at runtime. The editor will bring up the workbook but the entire workbook freezes.
I cannot click any of the buttons on the form, exit the form, input the range into the refedit box, or even click on any cells in the worksheet. Everything was working perfectly fine until I started adding command buttons to other pages of the multipage form, but no code has been added.
I don't even have code to link the pages together, all I did was add command buttons and change the names on them. I'm having a hard time believing this is what's causing excel to freeze up, but I haven't done anything else. I've checked my loops and they are fine.
I've also gone back to another copy and deleted everything on the other pages and left only page 1, but this is still failing.
I am having trouble with my workbook. When I am changing the value in the main cell ("Moses!B5") the whole program freezes. The code is as follows and was developed to ensure the values in my cells did not change as the main cell value changed:
Private Sub Worksheet_Calculate()
For Each c In Range("A1:AF14")
If c.Value = 1 Then c.Value = 1
Next
End Sub
I think the problem is that all of my sheets are calculating and I only want the one which contains the cell which matches my main cell ("Moses!B5"). Is there a way to trigger calculation only for the sheet which contains a date cell matching my main cell?
I have a Workbook that will freeze while trying to open.
Macro Security is set to Medium (klik "Disable Macro" )
The Hour Glass mouse pointer will stay on "Wait" then a "Not Responding" Alert will appear on the top blue bar of Excel.
The only way to shutdown Excel is by using the Task Manager ( Cnt_Alt+Del)
All other workbooks open OK on their own, but as soon as I trt to open the problem workbook, everything Excel freezes.
I have a little issue with a list-box. When i open my workbook and i click on the List Box the list box freezes, excel doesn't let me select it until i go to design mode, select the list box then close out of design mode. This sort of "fixes" the issue but i would rather not do so this way.
View 2 Replies View RelatedI have an issue with the excel freeze in our organisation.
Our organisation works on Citrix and the office applications have been upgraded to 2010 very recently.We have both 2003 excel users and 2010 excel users.There is a user who has a problem working on excel 2010 the excel freezes.Excel 2010 works fine for some time and all at a sudden hangs,once its hung it freezes all the other applications.
The same file runs properly on the other 2010 systems.The Excel file which hangs are also not of huge in size.
I am unable to get to the rootcause of the issue why the excel freezes or hangs in the middle of the work getting to the root cause of the issue and an answer for the excel hanging.
Background: The user makes a selection from a drop down box on the main sheet (sheet5, G12). The drop-down box is linked to *Sheet31.Pax_Nav*. If the drop-down box's linked cell value is less than 5, then do nothing (manual input required). If it is greater than 5, then the vlookup matches that number to a person in the database and returns their weight. The code will pull the required person's weight but then Excel will hang and freeze.
Private Sub Worksheet_Calculate()
On Error Resume Next
If Sheet31.Range("Pax_Nav") > 5 Then
Sheet5.Range("G12").Value = Application.WorksheetFunction.VLookup(Sheet31.Range("Pax_Nav").Value, Sheet31.Range("H17:L48"), 5, False)
Else
End If
End Sub
it would be possible to have text disappear after being displayed in Excel for a while.
My son and I played around with games (helping create a photographic memory, I told the youngster!) on the thing and until yesterday neither of us noticed that clicking the mouse anywhere (actually just clicking it) causes the screen to "freeze" while the code still happily erases the text in the background.
Hitting ESC allows the screen to cath up to the actual code EVEN THOUGH the ESC key is actually disabled from breaking the code.
I've managed to use some code I found to add a new row below the selected row, and duplicate all the forumlas of the source row. It worked fine dozens of times yesterday, but today it's decided not to work. It gets as far as creating the new row(s), but then just hangs & excel crashes before duplicating the formulas. I've even tried reverting to an earlier version, which also worked fine, but this crashes also!
Sub Add_New_Row()
' Unlock Worksheet
Worksheets("Sheet1").Unprotect Password:="*****"
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line
Dim sht As Worksheet, shts() As String, i As Integer
Redim shts(1 To Worksheets.Application. ActiveWorkbook. _
Windows(1).SelectedSheets.Count)..................................
I am using Excel 2010.
My objective is to pull specific values from an external file corresponding to the correct name and year of my choosing. The first way I thought would be best is to use an INDEX/MATCH function. The problem is the external files change names so I would need a method to easily change the source file name from one cell. I then stumbled across the INDIRECT function, but the INDIRECT function will only work when the source file is also opened. I then came across Harlan Grove's pull function which allows you to import data from closed excel files.
So, this is what my formula looks like: =INDEX(pull("'"&G12&"");MATCH(C15;pull("'"&G14&"");0);MATCH(D15;pull("'"&G16&"");0))
Cells G12,G14, and G16 contain the file paths for the ranges. C15 is name and D15 is year.
The problem I have though is that when I try to execute the function, Excel gets stuck. if the code cannot handle large amounts of data. I tested the code with a simple SUM function for a small range from an external file and it worked just fine.
This is the code I am using:
[Code] ...
I have an intensive VB program I've written that the user kicks off by clicking a button. I update a particular cell near the button with progress numbers so they can see what's going on. (This calculation can take up to 10 minutes.)
Sometimes, though, the UI stops updating. I know the calculation is still running because it completes eventually.
I am guessing Excel is getting too many updates to its screen (i.e. that cell) to keep up with the calculation going on in another thread, so it just gives up on the updating.
Is there a way to force Excel to keep updating the screen? I'm willing to give up the 0.01% slowdown in exchange for continuing to see progress. I see no need for the VB calculation thread to hog out the screen updater.
Excel 2010 on Win XP Pro SP3
We are attempting to use the macro below.
We believe that the problem is "memory related."
However, we can't find the correct workaround to the issue.
In Excel 2002, the behavior was for the macro to work great on small numbers of rows, but to just stop on large numbers of rows. After it stopped, the user would manually run the macro throughout the spreadsheet, by holding down Ctrl + N.
In Excel 2007, the behavior of the program is different, and Excel actually freezes up.
We've narrowed down the problem to be possibly "memory related."
However, we don't know the correct workaround for this.
For example, in the posting below (the referenced link) they suggest using "variant arrays" to address memory limitations type of issues ... but I'm not sure of how to implement those.
Im copying and pasting data from one workbook to another but when I want to close the source workbook, it comes up with this message that I have much data and if I want to keep this in a clipboard. I thought I could disable this with Application.DisplayAlerts = False but when I do this, Excel freezes. Im I doing something wrong. How can I supress this window?
Public path As String
Sub Get_data()
path = "\Nlchoosa.nlOPS_Processes$OPS_ProcessesReports Sector performance"
Workbooks.Open Filename:=path & "ReportsSector Performance Reporting week.xls"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Windows("Sector Performance report Week.xls").Activate
ActiveSheet.Paste
Application.DisplayClipboardWindow = False
Windows("Sector Performance Reporting week.xls").Activate
Application.DisplayAlerts = False
End Sub
I have created a userform and a command button to bring up the user form but when I click on the command button and the user form pops up I am not able to enter any data, the entire page freezes
This is the code
Private Sub CommandButtoncancel_Click()
unloadme
End Sub
Private Sub CommandButtonOK_Click()
With Workbooks("RETS results version 2.xlsm")
[Code] ......
This is weird - if you delete a sheet that contained a control then
a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End
b. public variables lose their value
These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?
I have one userform that loads combobox values upon userform Initialize. Though through a second userform changes can be made to anotherworkbook this workbook is saves any changes. when i close the second userform i need to rerun the 1st userform Initialize event to update the combobox's incase changes have been made.
View 5 Replies View RelatedI have several non-modal userforms in my App, some of them have date-fields that require manual entry typing of dd/mm/yy etc (No single userform has more than one date-box in it, this I think may be pivotally useful)
Now the Userform 'Calendar' that is built on the class of the same (cCalendar) name, has the write value line 'ActiveCell.value = theCal.value'
I'm looking to change this to refer to the correct userform.Textbox value, depending on which form is open.
I would imagine I could simply have a global string, whose value is set (or re-set) whenever a Userform is initialized (some sort of 'ActiveUF.value = Me.Name), where I get lost is referring to the components by name, so as to have a case statement by where I go:
Code:
Private Sub theCal_AfterUpdate()
Select Case ActiveUF
Case "AddForm"
application.vbe.components("AddForm").controls("AddFormDatePicker").value = theCal.value
Case "EditForm"
'.... etc
end select
end sub
better way of doing this (instead of passing around the userform name as a variable) - or proper syntax for referring to controls outside of the 'active' userform (but an open userform nonetheless)?
Every time I have to do this particular thing with userforms, I completely forget how, and the object browser always leads me on an infinite loop of Application.vbe.activevbproject.vbcomponents.vbe.active....
PS - there may be one slight complication to the process - one of the forms, has a 2-tab page in it, each page having similar (but named differently) fields. So I may need to be able to throw in 'Activepage' or whatnot
I Have a Userform which Have My Data i Print User Form Using Print Command Button And My Code Is
[Code] ......
But Its Printout Whole Form I only Wants To Print contents of Text Box's Or only Text From Userform TextBox. How To Print out Only Content of User Form Not The Whole Form ...
I'm trying to do: Let's say I have a simple userform with labels as follows:
(Label1) John's birthday: (Label2) 12.10.1974
(Label3) Mary's birthday: (Label4) 15.03.1978
(Button1) Change Birthdays
Let's say want to change the birthdays I see, so I press the button and enter the new values through another userform with inputboxes.
How do I make that change permanent, so that next time I start the macro in the labels 2 and 4 I have the last entered values for the birthdays???
What I would like to do is to be able to open a second workbook from a user form, preform some work on it then save and come back to the same place in the original user form.
So in steps:
1 : user clicks command button to open user form
2: user then clicks on command button on userform that opens 2nd workbook via a yes / no message box, but closes userform on 1st workbook (would be ideal if this could stay open, but hidden)
3: user then does work on 2nd workbook,
4: userform on 2nd workbook saves then activates the 1st workbook and reopens the userform
This is where no matter what I try I cant get the command button on the 1st userform to be clicked automatically so the yes / no message box appears.
This is part of the code in the 2nd sheet commandbutton that saves / closes / opens
Code:
Unload Me
ActiveWorkbook.save
Windows("ABC.xlsm").Activate
Sheets("Request Sheet").Activate
Call Sheets("Request Sheet").ForceClickOnBouttonXYZ
Call UserForm1.CommandButton6_Click 'this is where I cant get it to work!!
Windows("xyz.xlsm").Close
ABC is the 1st workbook
xyz is the 2nd workbook
This is the code on the 1st workbook I use to call on the 2nd workbook
Code:
Public Sub ForceClickOnBouttonXYZ() Call CommandButton1_Click End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Application.ScreenUpdating = True
Unload Me
RestrictedOptions.Show
Else
Cancel = True
End If
End Sub
Trying to use above code to unload active userform and show new userform when red X (close) selected by operator. With the code as is, the Unload Me leaves the form on the screen and displays the RestrictedOptions form. If i remove the Restrictedoptions.Show, the Unload Me does remove the original form.
When I run the userform initialize procedure to reset the values in text boxes and the like instead of resetting like it should, now it closes the userform completely and then won't allow me to show it again, what could be the problem?
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
TextBox3.Value = ActiveWorkbook. Sheets("Sales Invoice"). Range("G15").Value
Dim hWndForm As Long
Dim hMenu As Long
hWndForm = FindWindow("ThunderDFrame", Me.Caption) 'XL2000
hMenu = GetSystemMenu(hWndForm, 0)
DeleteMenu hMenu, SC_CLOSE, 0&
End Sub
The Dim stuff down is to gray out the x button, there are also some module level declarations to go with that...
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Private Const SC_CLOSE As Long = &HF060
the userform shows when the workbook opens and is then hidden when the user has finished entering values, then when the user goes through the process of being asked to print and save, they are then asked if they would like to create a new record, if yes then it shows the userform again, but the userform is still filled with all the stuff previously entered. I tried using the unload me instead of hiding and that wouldn't work at all, didn't give errors just didn't show the userform either, this at least shows the userform, but now when the user goes to clear the information by initializing the userform again, it simply closes the userform and then it can't be shown again either.
[url]
This is the link to the ZIP, and here are some instructions for setting it up to work.
The contents of this need to be unzipped into a folder called SyntheticShield that is placed in the C: drive that way all paths begin with C:SyntheticShield and then the other folders or files will be referenced correctly. The template I'm having problems with is the SyntheticShieldInvoiceMaker template, when you open it you'll be prompted whether you want to create a new invoice, number is final blah blah blah, say yes, then it will ask if you are importing from the Quotemaker which you aren't say no, it should then show the userform as it should. Then you can go through that process and by pressing either the create invoice or go to template directly whatever, you can hide the userform. Then you press the command button (red) a userform is brought up asking if you'd like to save without emailing, save with emailing or close controls, as for right now, I'm just getting the save without emailing to work the rest is all the same just a few tidbits of code. So click save without emailing, it should then prompt an are you sure message box, click yes it will do some things, then it will ask if you want to print, click no, then it will ask do you want to create a new invoice, click yes, this should then start the process all over again by calling the workbook_open procedure, however, when you go through everything, and click no to the import from quotemaker part it won't show the userform. And at one point it would, but then I couldn't initialize the userform without it disappearing and not being allowed to be shown...I tried putting a command button on the template to show the userform, but it wouldn't do it either.
how do i show a userform in Excel in the spreadsheet environment? I used to "run macro" but unable to run userform code "private sub".
View 2 Replies View Relatedhaving trouble with the details of actually making these features work for me. I figured out how to create a UserForm with a ListBox and 2 buttons, but I don't know how to proceed from here.
1. Populate the ListBox in the UserForm with a list of names from the sheet "Totals_Dropdowns", cells K2:K11
2. Make the UserForm pop up and enter the user's selection into cell C40 of the "Regenerate Request"
I know these are very basic operations, and I'm pretty sure I can figure out the rest of my problems once I can get past the above.
In order to deal with the multitude of screen resolutions that can be found at our work, I have got a module to check the current screen resolution on loading the workbook. I then have set up 3 different sized userforms, all containing the same coding, and based on the displayed screen resolution one of these is open (the userforms are named Customer1, Customer2, Customer3).
The displayed userform then has a button to show another userform (Customer_Details), and this form requires some referencing back to the previous form for some of it's information eg,
customer_details.textbox1.text = customer(1, 2 or 3).textbox1.text
customerdetails.combobox1.list = customer(1, 2 or 3).combobox.list
way for me reference the specific "Customer" form that has been opened, without calling it by name?
I have a userform button that when clicked loads another userform.
I am getting a Runtime Error 13: Type mismatch on the line in bold in the code below
Private Sub cmdAdd_Click()
Me.txtAdd.Value = Me.VarNo.Value
VariationsForm.Show
Unload Me
End Sub
I do not get an error on the "Edit" button code which is similar so why is the above not working.
Private Sub CmdEdit_Click()
Me.txtEdit.Value = Me.VarNo.Value
VariationsForm.Show
Unload Me
End Sub
I want to use the following to launch a userform I created.
Sub showuserform()
Dim ans As Boolean
ans = msgbox("do you want to show the userform?", vbYesno)
If ans = vbYes Then
userform.show
End If
End Sub
The trouble is that if I click yes, nothing happens.
I am trying to change the property value of all optionbuttons on userform1 via userform2's deactivate event
Private Sub UserForm2_Deactivate()
If userform1.Controls = OptionButton Then
Value = False
End If
End Sub
I want to populate a textbox (output) on one userform with data from a textbox (target) on another userform.
The data in the textbox (target) is the result of calculations in the userform code and is not gathered from or saved to a cell.
I want the textbox (output) to be populated at "Userform intialize" event.
I have tried various other methods without any success.