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