Macro not ending?

[PamW]

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

 

[Mike]

Posting some or all of your code will help others help you.

 

[Joel]

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.

 

[Pam]

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.SortFields.Clear
ActiveWorkbook.Worksheets("Sample").Sort.SortFields.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.

 

[Joel]

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]

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

 

[Pam]

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.

 

[Pam]

I found the answer on another post from today. I have to unload the form.
That worked. Thank you!

 

[Joel]

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]

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