Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
I've been having a lot of problems and it seems to be that the macro works
perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
Posting some or all of your code will help others help you.
"PamW" wrote: I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
The activesheet may be different the frist time you run the code from the
second time. Make sure you specify each sheet by name or number rather than activesheet if you have more than one sheet. "Mike" wrote: Posting some or all of your code will help others help you. "PamW" wrote: I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
The beginning module runs this sub. It erases data in a table in a separate
sheet ("Sample") and updates a column for a dropdown combobox. The second time, it calls up a form, "EnterMenu, which has a control button to call up the dropdown combobox. You select the number you want, and it pulls the data from the Access file into "Sample" sheet which is then copied to "Estimate" sheet that the user sees. 'Finds all data (Used to provide all project numbers to dropdown values) Dim sWHERE As String Dim sAccessFile As String Dim sTable As String Dim lastrow As String Dim numrows As String 'Hides viewing of running macro Application.ScreenUpdating = False 'Copies data in rows a-j to "Sample" worksheet Sheets("Sample").Select Range("A2:j2").Select Selection.ClearContents sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb" sTable = "All Fields" ' Get rid of existing data in the table starting at row 5 ClearTableData 5 ' Construct a query using data in the table starting at row 2 ConstructQuery 1, sWHERE ' Get query data from Access and put it in the table starting at row 5 QueryAccessToDataTable 5, sAccessFile, sTable, sWHERE 'Sorts all project nos. Worksheets("Sample").Select Range("A5").Select lastrow = Selection.End(xlDown).row ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Add Key:=Range("A6:A" & lastrow), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sample").Sort .SetRange Range("A5:A" & lastrow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Filters project nos and copies them to Drowdown Values sheet Range("A5:A" & lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("a6").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select numrows = Range("A6:A" & lastrow).Rows.SpecialCells(xlVisible).Count Selection.Copy Sheets("Dropdown Values").Select Range("A2").Select ActiveSheet.Paste Application.CutCopyMode = False 'Deletes anything below updated range of Project Numbers Range("A" & (numrows + 1)).Select ActiveCell.Offset(1, 0).Range("A1").Select If ActiveCell.Text = "" Then Else Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents End If Sheets("Sample").Select Range("A6").Select Selection.AutoFilter Selection.AutoFilter Sheets("Estimate").Select 'EnterMenu.StartUpPosition = CenterOwner EnterMenu.Show End Sub The second time it doesn't do the initialize code in the EnterMenu form. It just shows then menu and then ends the original sub. The combobox is not cleared, and the forms shift to the left side of the screen. "Mike" wrote: Posting some or all of your code will help others help you. "PamW" wrote: I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
The code is ending becuase of the combobox 1. I can't tell what ends the
Userform. Is it somebody pressing a control button? otr is it somebody entering/selecting data in the combobox. Maybe you need to unselect the Selected Item in the Comboxbox using this statement in the code you sent UserForm1.ComboBox1.ListIndex = -1 setting the ListIndex to -1 unselects the selected item. "Pam" wrote: The beginning module runs this sub. It erases data in a table in a separate sheet ("Sample") and updates a column for a dropdown combobox. The second time, it calls up a form, "EnterMenu, which has a control button to call up the dropdown combobox. You select the number you want, and it pulls the data from the Access file into "Sample" sheet which is then copied to "Estimate" sheet that the user sees. 'Finds all data (Used to provide all project numbers to dropdown values) Dim sWHERE As String Dim sAccessFile As String Dim sTable As String Dim lastrow As String Dim numrows As String 'Hides viewing of running macro Application.ScreenUpdating = False 'Copies data in rows a-j to "Sample" worksheet Sheets("Sample").Select Range("A2:j2").Select Selection.ClearContents sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb" sTable = "All Fields" ' Get rid of existing data in the table starting at row 5 ClearTableData 5 ' Construct a query using data in the table starting at row 2 ConstructQuery 1, sWHERE ' Get query data from Access and put it in the table starting at row 5 QueryAccessToDataTable 5, sAccessFile, sTable, sWHERE 'Sorts all project nos. Worksheets("Sample").Select Range("A5").Select lastrow = Selection.End(xlDown).row ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Add Key:=Range("A6:A" & lastrow), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sample").Sort .SetRange Range("A5:A" & lastrow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Filters project nos and copies them to Drowdown Values sheet Range("A5:A" & lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("a6").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select numrows = Range("A6:A" & lastrow).Rows.SpecialCells(xlVisible).Count Selection.Copy Sheets("Dropdown Values").Select Range("A2").Select ActiveSheet.Paste Application.CutCopyMode = False 'Deletes anything below updated range of Project Numbers Range("A" & (numrows + 1)).Select ActiveCell.Offset(1, 0).Range("A1").Select If ActiveCell.Text = "" Then Else Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents End If Sheets("Sample").Select Range("A6").Select Selection.AutoFilter Selection.AutoFilter Sheets("Estimate").Select 'EnterMenu.StartUpPosition = CenterOwner EnterMenu.Show End Sub The second time it doesn't do the initialize code in the EnterMenu form. It just shows then menu and then ends the original sub. The combobox is not cleared, and the forms shift to the left side of the screen. "Mike" wrote: Posting some or all of your code will help others help you. "PamW" wrote: I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
Below is the code for the combobox. I'm not sure where to put
"ProjectNoMenu.ProjectNumberComboBox.ListIndex = -1" since the combobox run on change event, wherever I put it, it will start the combobox. Private Sub ProjectNumberComboBox_Change() 'Brings up combo box with project numbers from column on "DropDown Values" worksheet ' Dim PrNo As String PrNo = ProjectNumberComboBox.Text Sheets("Sample").Select Range("a2") = PrNo ProjectNoMenu.Hide ContinueSearchData1 End Sub Private Sub UserForm_Initialize() Dim rowx As String Dim PNRng As Range Dim z As Variant 'ProjectNoMenu.StartUpPosition = CenterOwner For Each C In ProjectNoMenu.Controls If TypeOf C Is MSForms.ComboBox Then C.Value = "" End If Next Sheets("Dropdown Values").Select rowx = Range("A2").End(xlDown).row 'Set the range to loop through Set PNRng = Sheets("Dropdown Values").Range("A2:A" & rowx) 'Loops through the ranges For Each z In PNRng 'Adds an item to the list ProjectNumberComboBox.AddItem z.Value Next z Sheets("Estimate").Select End Sub "Joel" wrote: The code is ending becuase of the combobox 1. I can't tell what ends the Userform. Is it somebody pressing a control button? otr is it somebody entering/selecting data in the combobox. Maybe you need to unselect the Selected Item in the Comboxbox using this statement in the code you sent UserForm1.ComboBox1.ListIndex = -1 setting the ListIndex to -1 unselects the selected item. "Pam" wrote: The beginning module runs this sub. It erases data in a table in a separate sheet ("Sample") and updates a column for a dropdown combobox. The second time, it calls up a form, "EnterMenu, which has a control button to call up the dropdown combobox. You select the number you want, and it pulls the data from the Access file into "Sample" sheet which is then copied to "Estimate" sheet that the user sees. 'Finds all data (Used to provide all project numbers to dropdown values) Dim sWHERE As String Dim sAccessFile As String Dim sTable As String Dim lastrow As String Dim numrows As String 'Hides viewing of running macro Application.ScreenUpdating = False 'Copies data in rows a-j to "Sample" worksheet Sheets("Sample").Select Range("A2:j2").Select Selection.ClearContents sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb" sTable = "All Fields" ' Get rid of existing data in the table starting at row 5 ClearTableData 5 ' Construct a query using data in the table starting at row 2 ConstructQuery 1, sWHERE ' Get query data from Access and put it in the table starting at row 5 QueryAccessToDataTable 5, sAccessFile, sTable, sWHERE 'Sorts all project nos. Worksheets("Sample").Select Range("A5").Select lastrow = Selection.End(xlDown).row ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Add Key:=Range("A6:A" & lastrow), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sample").Sort .SetRange Range("A5:A" & lastrow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Filters project nos and copies them to Drowdown Values sheet Range("A5:A" & lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("a6").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select numrows = Range("A6:A" & lastrow).Rows.SpecialCells(xlVisible).Count Selection.Copy Sheets("Dropdown Values").Select Range("A2").Select ActiveSheet.Paste Application.CutCopyMode = False 'Deletes anything below updated range of Project Numbers Range("A" & (numrows + 1)).Select ActiveCell.Offset(1, 0).Range("A1").Select If ActiveCell.Text = "" Then Else Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents End If Sheets("Sample").Select Range("A6").Select Selection.AutoFilter Selection.AutoFilter Sheets("Estimate").Select 'EnterMenu.StartUpPosition = CenterOwner EnterMenu.Show End Sub The second time it doesn't do the initialize code in the EnterMenu form. It just shows then menu and then ends the original sub. The combobox is not cleared, and the forms shift to the left side of the screen. "Mike" wrote: Posting some or all of your code will help others help you. "PamW" wrote: I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
I should clarify, the first time it runs perfect. The second time as soon as
it comes on the combobox list index equals -1, it jumps to the combobox code. I know I must be missing something simple, but I don't have the knowledge to figure it out. "Joel" wrote: The code is ending becuase of the combobox 1. I can't tell what ends the Userform. Is it somebody pressing a control button? otr is it somebody entering/selecting data in the combobox. Maybe you need to unselect the Selected Item in the Comboxbox using this statement in the code you sent UserForm1.ComboBox1.ListIndex = -1 setting the ListIndex to -1 unselects the selected item. "Pam" wrote: The beginning module runs this sub. It erases data in a table in a separate sheet ("Sample") and updates a column for a dropdown combobox. The second time, it calls up a form, "EnterMenu, which has a control button to call up the dropdown combobox. You select the number you want, and it pulls the data from the Access file into "Sample" sheet which is then copied to "Estimate" sheet that the user sees. 'Finds all data (Used to provide all project numbers to dropdown values) Dim sWHERE As String Dim sAccessFile As String Dim sTable As String Dim lastrow As String Dim numrows As String 'Hides viewing of running macro Application.ScreenUpdating = False 'Copies data in rows a-j to "Sample" worksheet Sheets("Sample").Select Range("A2:j2").Select Selection.ClearContents sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb" sTable = "All Fields" ' Get rid of existing data in the table starting at row 5 ClearTableData 5 ' Construct a query using data in the table starting at row 2 ConstructQuery 1, sWHERE ' Get query data from Access and put it in the table starting at row 5 QueryAccessToDataTable 5, sAccessFile, sTable, sWHERE 'Sorts all project nos. Worksheets("Sample").Select Range("A5").Select lastrow = Selection.End(xlDown).row ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Add Key:=Range("A6:A" & lastrow), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sample").Sort .SetRange Range("A5:A" & lastrow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Filters project nos and copies them to Drowdown Values sheet Range("A5:A" & lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("a6").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select numrows = Range("A6:A" & lastrow).Rows.SpecialCells(xlVisible).Count Selection.Copy Sheets("Dropdown Values").Select Range("A2").Select ActiveSheet.Paste Application.CutCopyMode = False 'Deletes anything below updated range of Project Numbers Range("A" & (numrows + 1)).Select ActiveCell.Offset(1, 0).Range("A1").Select If ActiveCell.Text = "" Then Else Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents End If Sheets("Sample").Select Range("A6").Select Selection.AutoFilter Selection.AutoFilter Sheets("Estimate").Select 'EnterMenu.StartUpPosition = CenterOwner EnterMenu.Show End Sub The second time it doesn't do the initialize code in the EnterMenu form. It just shows then menu and then ends the original sub. The combobox is not cleared, and the forms shift to the left side of the screen. "Mike" wrote: Posting some or all of your code will help others help you. "PamW" wrote: I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
I found the answer on another post from today. I have to unload the form.
That worked. Thank you! "Pam" wrote: I should clarify, the first time it runs perfect. The second time as soon as it comes on the combobox list index equals -1, it jumps to the combobox code. I know I must be missing something simple, but I don't have the knowledge to figure it out. "Joel" wrote: The code is ending becuase of the combobox 1. I can't tell what ends the Userform. Is it somebody pressing a control button? otr is it somebody entering/selecting data in the combobox. Maybe you need to unselect the Selected Item in the Comboxbox using this statement in the code you sent UserForm1.ComboBox1.ListIndex = -1 setting the ListIndex to -1 unselects the selected item. "Pam" wrote: The beginning module runs this sub. It erases data in a table in a separate sheet ("Sample") and updates a column for a dropdown combobox. The second time, it calls up a form, "EnterMenu, which has a control button to call up the dropdown combobox. You select the number you want, and it pulls the data from the Access file into "Sample" sheet which is then copied to "Estimate" sheet that the user sees. 'Finds all data (Used to provide all project numbers to dropdown values) Dim sWHERE As String Dim sAccessFile As String Dim sTable As String Dim lastrow As String Dim numrows As String 'Hides viewing of running macro Application.ScreenUpdating = False 'Copies data in rows a-j to "Sample" worksheet Sheets("Sample").Select Range("A2:j2").Select Selection.ClearContents sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb" sTable = "All Fields" ' Get rid of existing data in the table starting at row 5 ClearTableData 5 ' Construct a query using data in the table starting at row 2 ConstructQuery 1, sWHERE ' Get query data from Access and put it in the table starting at row 5 QueryAccessToDataTable 5, sAccessFile, sTable, sWHERE 'Sorts all project nos. Worksheets("Sample").Select Range("A5").Select lastrow = Selection.End(xlDown).row ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Add Key:=Range("A6:A" & lastrow), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sample").Sort .SetRange Range("A5:A" & lastrow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Filters project nos and copies them to Drowdown Values sheet Range("A5:A" & lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("a6").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select numrows = Range("A6:A" & lastrow).Rows.SpecialCells(xlVisible).Count Selection.Copy Sheets("Dropdown Values").Select Range("A2").Select ActiveSheet.Paste Application.CutCopyMode = False 'Deletes anything below updated range of Project Numbers Range("A" & (numrows + 1)).Select ActiveCell.Offset(1, 0).Range("A1").Select If ActiveCell.Text = "" Then Else Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents End If Sheets("Sample").Select Range("A6").Select Selection.AutoFilter Selection.AutoFilter Sheets("Estimate").Select 'EnterMenu.StartUpPosition = CenterOwner EnterMenu.Show End Sub The second time it doesn't do the initialize code in the EnterMenu form. It just shows then menu and then ends the original sub. The combobox is not cleared, and the forms shift to the left side of the screen. "Mike" wrote: Posting some or all of your code will help others help you. "PamW" wrote: I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
Here are a few comments
1) I ws the one who posted the UNLOAD suggestion. I don't think this is really that correct solution but it worked 2) the line "UserForm1.ComboBox1.ListIndex = -1" was meant for your main code not the userform code. It would unselect the present item in the combobox. 3) "Hide" the combobox stops the code from running. the last line in the code below never executes ProjectNoMenu.Hide ContinueSearchData1 I also think in your combobox change function you should check to see if any thing is selected before the hide if ProjectNumberComboBox.listindex < -1 then ProjectNoMenu.Hide end if "Pam" wrote: I found the answer on another post from today. I have to unload the form. That worked. Thank you! "Pam" wrote: I should clarify, the first time it runs perfect. The second time as soon as it comes on the combobox list index equals -1, it jumps to the combobox code. I know I must be missing something simple, but I don't have the knowledge to figure it out. "Joel" wrote: The code is ending becuase of the combobox 1. I can't tell what ends the Userform. Is it somebody pressing a control button? otr is it somebody entering/selecting data in the combobox. Maybe you need to unselect the Selected Item in the Comboxbox using this statement in the code you sent UserForm1.ComboBox1.ListIndex = -1 setting the ListIndex to -1 unselects the selected item. "Pam" wrote: The beginning module runs this sub. It erases data in a table in a separate sheet ("Sample") and updates a column for a dropdown combobox. The second time, it calls up a form, "EnterMenu, which has a control button to call up the dropdown combobox. You select the number you want, and it pulls the data from the Access file into "Sample" sheet which is then copied to "Estimate" sheet that the user sees. 'Finds all data (Used to provide all project numbers to dropdown values) Dim sWHERE As String Dim sAccessFile As String Dim sTable As String Dim lastrow As String Dim numrows As String 'Hides viewing of running macro Application.ScreenUpdating = False 'Copies data in rows a-j to "Sample" worksheet Sheets("Sample").Select Range("A2:j2").Select Selection.ClearContents sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb" sTable = "All Fields" ' Get rid of existing data in the table starting at row 5 ClearTableData 5 ' Construct a query using data in the table starting at row 2 ConstructQuery 1, sWHERE ' Get query data from Access and put it in the table starting at row 5 QueryAccessToDataTable 5, sAccessFile, sTable, sWHERE 'Sorts all project nos. Worksheets("Sample").Select Range("A5").Select lastrow = Selection.End(xlDown).row ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Add Key:=Range("A6:A" & lastrow), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sample").Sort .SetRange Range("A5:A" & lastrow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Filters project nos and copies them to Drowdown Values sheet Range("A5:A" & lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("a6").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select numrows = Range("A6:A" & lastrow).Rows.SpecialCells(xlVisible).Count Selection.Copy Sheets("Dropdown Values").Select Range("A2").Select ActiveSheet.Paste Application.CutCopyMode = False 'Deletes anything below updated range of Project Numbers Range("A" & (numrows + 1)).Select ActiveCell.Offset(1, 0).Range("A1").Select If ActiveCell.Text = "" Then Else Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents End If Sheets("Sample").Select Range("A6").Select Selection.AutoFilter Selection.AutoFilter Sheets("Estimate").Select 'EnterMenu.StartUpPosition = CenterOwner EnterMenu.Show End Sub The second time it doesn't do the initialize code in the EnterMenu form. It just shows then menu and then ends the original sub. The combobox is not cleared, and the forms shift to the left side of the screen. "Mike" wrote: Posting some or all of your code will help others help you. "PamW" wrote: I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not ending?
1. I agree.
2. That was in my main code, but the second time I ran the macro, when it came upon that code, I guess it saw it as a change event, and it jumped to the combobox code. 3. But it ran the sub continue searchdata1. I will include the code you suggested in my combo box. Thank you so much for your help. You don't know how much it is appreciated! Pam "Joel" wrote: Here are a few comments 1) I ws the one who posted the UNLOAD suggestion. I don't think this is really that correct solution but it worked 2) the line "UserForm1.ComboBox1.ListIndex = -1" was meant for your main code not the userform code. It would unselect the present item in the combobox. 3) "Hide" the combobox stops the code from running. the last line in the code below never executes ProjectNoMenu.Hide ContinueSearchData1 I also think in your combobox change function you should check to see if any thing is selected before the hide if ProjectNumberComboBox.listindex < -1 then ProjectNoMenu.Hide end if "Pam" wrote: I found the answer on another post from today. I have to unload the form. That worked. Thank you! "Pam" wrote: I should clarify, the first time it runs perfect. The second time as soon as it comes on the combobox list index equals -1, it jumps to the combobox code. I know I must be missing something simple, but I don't have the knowledge to figure it out. "Joel" wrote: The code is ending becuase of the combobox 1. I can't tell what ends the Userform. Is it somebody pressing a control button? otr is it somebody entering/selecting data in the combobox. Maybe you need to unselect the Selected Item in the Comboxbox using this statement in the code you sent UserForm1.ComboBox1.ListIndex = -1 setting the ListIndex to -1 unselects the selected item. "Pam" wrote: The beginning module runs this sub. It erases data in a table in a separate sheet ("Sample") and updates a column for a dropdown combobox. The second time, it calls up a form, "EnterMenu, which has a control button to call up the dropdown combobox. You select the number you want, and it pulls the data from the Access file into "Sample" sheet which is then copied to "Estimate" sheet that the user sees. 'Finds all data (Used to provide all project numbers to dropdown values) Dim sWHERE As String Dim sAccessFile As String Dim sTable As String Dim lastrow As String Dim numrows As String 'Hides viewing of running macro Application.ScreenUpdating = False 'Copies data in rows a-j to "Sample" worksheet Sheets("Sample").Select Range("A2:j2").Select Selection.ClearContents sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb" sTable = "All Fields" ' Get rid of existing data in the table starting at row 5 ClearTableData 5 ' Construct a query using data in the table starting at row 2 ConstructQuery 1, sWHERE ' Get query data from Access and put it in the table starting at row 5 QueryAccessToDataTable 5, sAccessFile, sTable, sWHERE 'Sorts all project nos. Worksheets("Sample").Select Range("A5").Select lastrow = Selection.End(xlDown).row ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sample").Sort.SortField s.Add Key:=Range("A6:A" & lastrow), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sample").Sort .SetRange Range("A5:A" & lastrow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Filters project nos and copies them to Drowdown Values sheet Range("A5:A" & lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("a6").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select numrows = Range("A6:A" & lastrow).Rows.SpecialCells(xlVisible).Count Selection.Copy Sheets("Dropdown Values").Select Range("A2").Select ActiveSheet.Paste Application.CutCopyMode = False 'Deletes anything below updated range of Project Numbers Range("A" & (numrows + 1)).Select ActiveCell.Offset(1, 0).Range("A1").Select If ActiveCell.Text = "" Then Else Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents End If Sheets("Sample").Select Range("A6").Select Selection.AutoFilter Selection.AutoFilter Sheets("Estimate").Select 'EnterMenu.StartUpPosition = CenterOwner EnterMenu.Show End Sub The second time it doesn't do the initialize code in the EnterMenu form. It just shows then menu and then ends the original sub. The combobox is not cleared, and the forms shift to the left side of the screen. "Mike" wrote: Posting some or all of your code will help others help you. "PamW" wrote: I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ending Macro in Last Row within a column of Printable Items | Excel Worksheet Functions | |||
remove duplicates macro ending sub | Excel Programming | |||
Ending a macro? | Excel Programming | |||
Ending Looping Macro | Excel Programming | |||
macro not ending | Excel Programming |