JLGWhiz wrote:
O.K. Let's try this:

Private Sub UserForm_Initialize()
Dim i As Long
If Me.ComboBox1.ListCount < 0 Then
Me.ComboBox1.Clear
End If
For i = 1 To ActiveWorkbook.Worksheets.Count
Me.ComboBox1.AddItem ActiveWorkbook.Worksheets(i).Name
Next
ComboBox1.Value = ActiveWorkbook.Worksheets(1).Name
End Sub

I don't understand why the control would still have sheets listed since the
unload should clear it when you select and click the command button. But, to
make sure it is cleared, we can test it and if not we will clear it.



"jbarrington" <"jbarrington -at- comcast" wrote:

JLGWhiz wrote:
Missed one. All worksheet references need to be qualified
since you are switching to a workbook that does not contain
the code.

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To ActiveWorkbook.Worksheets.Count
Me.ComboBox1.AddItem ActiveWorkbook.Worksheets(i).Name
Next
ComboBox1.Value = ActiveWorkbook.Worksheets(1).Name
End Sub


"jbarrington" <"jbarrington -at- comcast" wrote:

I'm new to userforms and can't find a solution.

I'm wanting to use this rough-draft macro and userform to let get me
choose a worksheet of a workbook from a combo box, and then reuse it
again to let me choose a worksheet from another workbook in the combo box.

It seems to work ok the first time around, but the second time the combo
box wants to display the same worksheets from the first time around, and
then goes to an error.

Can someone tell me what's wrong?
Thanks.

==================
Here's the macro:
==================

Sub test()

Dim MainWB As String
Dim wbDestination As String
Dim wbReference As String
Dim CellValue As String
Dim tester As String

MainWB = ActiveWorkbook.Name
wbDestination = Sheets("Sheet1").Range("B4").Value
wbReference = Sheets("Sheet1").Range("B4").Value

Windows(MainWB).Activate
CellValue = Sheets("Sheet1").Range("B4").Value
CellLocation = "B4"

If CellValue = "" Then
Windows("1stWorkbook.xls").Activate
frmDropIt.Show
tester = frmDropIt.ComboBox1.Value
Windows(MainWB).Activate
Range(CellLocation).Value = tester
Else
'Proceed to make sure the worksheet is the primary choice
End If

Windows(MainWB).Activate
CellValue = Sheets("Sheet1").Range("B7").Value
CellLocation = "B7"

If CellValue = "" Then
Windows("2ndWorkbook.xls").Activate
frmDropIt.Show
tester = frmDropIt.ComboBox1.Value
Windows(MainWB).Activate
Range(CellLocation).Value = tester
Else
'Proceed to make sure the worksheet is the primary choice
End If
End Sub

==================
Here's the Form:
==================

Private Sub cmdOK_Click()
'Dim ReturnThis As String

If Me.ComboBox1.Value = "" Then
Unload Me
Else
Worksheets(Me.ComboBox1.Value).Activate
'MsgBox "You chose: " & Me.ComboBox1.Value
Unload Me
End If
End Sub

Private Sub ComboBox1_Change()
'No code here
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim i As Long

For i = 1 To Worksheets.Count
Me.ComboBox1.AddItem Worksheets(i).Name
Next
ComboBox1.Value = Worksheets(1).Name
End Sub

First of all, thank you for replying.

Nope. It didn't work. The combo box still works ok the first time around
for displaying the worksheets from the 1st workbook, but on the second
time, the combo box still holds on to the worksheets of the 1st workbook
instead of dropping them and now show the worksheets from the 2nd workbook.


Thank you again for helping. This userform issue has been a brick wall
for me as well as a learning experience in this new level of creating
macros. :-)

It's still the same as far as seeming to work the first time around, and
then not working properly the second.

I'm not sure if this will help or not, but what I'm trying to do is
this.I have three workbooks that have been already opened. The main
workbook contains the macro. The reference workbook will have data to be
collected. The destination workbook will hold the collected data.

The macro will look on a worksheet in the main workbook to see if a
worksheet is named from the destination workbook. If one isn't named,
then it opens a userform and shows the existing worksheets within the
destination workbook. If the user selects a worksheet, the name is
placed in a cell in the main workbook so that it can be reference again
later if the workbook is opened again.

It does this routine (sub and userform) again if the reference workbook
doesn't have a worksheet listed in the main workbook too.

I also get an error.

Run-time error '9':
Subscript out of range

Highlights within the Private Sub cmdOK_Click():
Worksheets(Me.ComboBox1.Value).Activate