JLGWhiz wrote:
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
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''