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
This newsgroup is for issues with UserForms in Word. I would suggest that you post your question to the Microsoft.public.excel.programming newsgroup as it is probably more of an Excel issue rather than something to do with the UserForm itself. You should indicate what line of code is causing the error when that happens. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "jbarrington" <"jbarrington -at- comcast period net"> wrote in message news:H7CdnaeUMKdTdHXVnZ2dnUVZ_gSdnZ2d@comcast.com... > 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
Doug Robbins - Word MVP wrote: > This newsgroup is for issues with UserForms in Word. I would suggest that > you post your question to the Microsoft.public.excel.programming newsgroup > as it is probably more of an Excel issue rather than something to do with > the UserForm itself. > > You should indicate what line of code is causing the error when that > happens. > OOPS!!! Apologies.
