Newbie: Problem with re-using a combo-box in userform

[jbarrington]

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

 

[JLGWhiz]

I think your problem is in the initialize event code. Try this:

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 = Worksheets(1).Name
End Sub

 

[JLGWhiz]

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]

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

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.

 

[JLGWhiz]

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]

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.

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

 

[JLGWhiz]

Run-time error '9':

Subscript out of range

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

It is telling you that the value of ComboBox1 does not equate to a
recognizable object. If you step through the code, using F8 key, to check
the code line by line your tool tips popup will show the values of your
objects and variables when you mouse-over them. Set a breakpoint on the
first line of the code after the title line and then click the button to
initiate the code. Then you can use F8 to step through the rest of the macro.

I will take a closer look at your other problem and see if I can find
something that I might have overlooked initially. Right now, I don't
understand why it is not working with the changes I gave you.

 

[JLGWhiz]

I think I have identified the source of your problem
The code is executing exactly as it is written, so
to fix the problem, you will need to rearrange the
code somewhat. First the cause of the problem.

In your UserForm code you have this:

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

Since you unload the form and then call for the combobox
value from the main macro, it has to go back to the
initialize event to get that value and the last line
makes it always be "Sheet1". The only way around this
is to leave the form open while the combobox value is
being retrieved. But if you try that it can cause a
different problem. So, my suggestion would be to
rearrange the code a little bit.

Where you are using this line

tester = frmDropIt.ComboBox1.Value

to capture the value of the combobox and then to
put that value in eithe B4 of the active sheet of one
workbook or B7 of the other workbook, you would need to
move this operation into the click event code of your
UserForm command button. This will execute the posting
of the values while the form is open and the selected
values are still valid on the ComboBox. Since you do not
have to activate a workbook and sheet to post to them,
you can probably use an If...Then...Else statement to
evaluate which needs to be posted, simply Use:
Workbooks("Whichever").Sheets("Selected").Range("B?") =
Me.ComboBox1.Value. Fill in your actual workbook names,
sheet name, and range accordingly. Then when the value
is posted to the sheet, unload the form.

If you work with this a while and still cannot get it
to work. Make a new posting and ask for some more help.
But I think, now that you know where the problem is, you
can probably solve it.

 

[jbarrington]

I think I have identified the source of your problem
The code is executing exactly as it is written, so
to fix the problem, you will need to rearrange the
code somewhat. First the cause of the problem.

In your UserForm code you have this:

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

Since you unload the form and then call for the combobox
value from the main macro, it has to go back to the
initialize event to get that value and the last line
makes it always be "Sheet1". The only way around this
is to leave the form open while the combobox value is
being retrieved. But if you try that it can cause a
different problem. So, my suggestion would be to
rearrange the code a little bit.

Where you are using this line

tester = frmDropIt.ComboBox1.Value

to capture the value of the combobox and then to
put that value in eithe B4 of the active sheet of one
workbook or B7 of the other workbook, you would need to
move this operation into the click event code of your
UserForm command button. This will execute the posting
of the values while the form is open and the selected
values are still valid on the ComboBox. Since you do not
have to activate a workbook and sheet to post to them,
you can probably use an If...Then...Else statement to
evaluate which needs to be posted, simply Use:
Workbooks("Whichever").Sheets("Selected").Range("B?") =
Me.ComboBox1.Value. Fill in your actual workbook names,
sheet name, and range accordingly. Then when the value
is posted to the sheet, unload the form.

If you work with this a while and still cannot get it
to work. Make a new posting and ask for some more help.
But I think, now that you know where the problem is, you
can probably solve it.

I want to say thanks for the help! You tried your best to give me a hand!

I think that I finally figured out how to accomplish it, and I wanted to
post it here in case it can help someone else. I ended up finding
something on the web that pointed me in the direction. I could be wrong
but my problem seems to have came from passing information within
variables. Placing a sub routine within the userform seemed to help.

This hint helped came from this site:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=470

First of all, I want to state this code is still a rough draft and that
it most likely can be done differently and/or better. I may tweak it later.

Here's the routine that seems to be working for me:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
This portion goes into a module
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub OpenCourseBookingForm()

Dim WbData As String
Dim WsData As String

'==========================
'This gets the chosen worksheet name from the first workbook
'and places it in the main workbook's worksheet cell
'==========================
WbData = Range("A2").Value
WsData = "B2"

Windows(WbData).Activate

' MUST load the form first!
Load frmCourseBooking
' Send the variables over to the form
Call frmCourseBooking.FillVars(WbData, WsData)
' Now show the form
frmCourseBooking.Show

'==========================
'This gets the chosen worksheet name from the second workbook
'and places it in the main workbook's worksheet cell
'==========================

WbData = Range("A1").Value
WsData = "B1"

Windows(WbData).Activate

' MUST load the form first!
Load frmCourseBooking
' Send the variables over to the form
Call frmCourseBooking.FillVars(WbData, WsData)
' Now show the form
frmCourseBooking.Show

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
NOTE: All of the next code below goes into the userform
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim ws As String
Dim wb As String

Sub FillVars(ByRef WbData As String, ByRef WsData As String)
' This hint came from:
' http://www.vbaexpress.com/kb/getarticle.php?kb_id=470

' This sub collects the variables from the calling module.
' Make sure it's not marked 'Private'.
'=============================================================
' Any form initialization that relies on external variables
' should be done here.

'Label1.Caption = WbData
'Label2.Caption = WsData
'==============================================================
' wb and ws are not visible to other Subs in the form,
' so their contents are passed to str1 and str2 before leaving.
wb = WbData
ws = WsData
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdOK_Click()
Dim tester As String
tester = cboDepartment.Value
ThisWorkbook.Activate
Range(ws).Value = tester
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Worksheets.Count
Me.cboDepartment.AddItem "'" & Worksheets(i).Name
Next
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''