| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual basic 6.5 question using combo box and call procedure.
Details: I have over 100 identical worksheets with the same combo box user
control scrollbar on each Excel worksheet to allow user to choose from one of 6 print options. I want to use a call procedure in each visual basic sheet so that I can maintain the detailed print option procedures in one module rather than in each sheet. I successfully experimented with a command button using the click event control that called a procedure in another module. The only difference I can see is that my combo box uses the change event control with 6 different events based on the user selection. I cant figure out the syntax to make the call procedure work. Hopefully the lingo I used is accurate in describing my question. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual basic 6.5 question using combo box and call procedure.
Most of what you wrote made sense. I'm attempting to use your 2nd suggestion
so that it is less work ("If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name)." I'm confused about how to use a combobox from the control toolbox and use a button from the Forms toolbar. It seems like I'll end up with 2 control buttons on each worksheet. Below is the code I have at this point. Sub Print_Charts() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub Thanks. Terry "Dave Peterson" wrote: If you used a commandbutton from thecontrol toolbox toolbar, each one of those commandbuttons will have to call the common procedure. If you used a combobox from the Control toolbox toolbar on each sheet (not a dropdown from the Forms toolbar), and you gave each of the comboboxes the same name. The code behind the worksheet that the commandbutton uses would look like: Option Explicit Private Sub CommandButton1_Click() Call DoTheWork(Me.ComboBox1) End Sub And the code in a general module would look like: Option Explicit Sub DoTheWork(CBX As MSForms.ComboBox) With CBX 'just to show you that you got the right one 'MsgBox .Name & vbLf & .Parent.Name If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") .Parent.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub =========== If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name). Option Explicit Sub DoTheWork() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("Combobox1") With CBX.Object If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") ActiveSheet.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub TAS wrote: Details: I have over 100 identical worksheets with the same combo box user control scrollbar on each Excel worksheet to allow user to choose from one of 6 print options. I want to use a call procedure in each visual basic sheet so that I can maintain the detailed print option procedures in one module rather than in each sheet. I successfully experimented with a command button using the âœclickâ event control that called a procedure in another module. The only difference I can see is that my combo box uses the âœchangeâ event control with 6 different events based on the user selection. I canât figure out the syntax to make the call procedure work. Hopefully the lingo I used is accurate in describing my question. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual basic 6.5 question using combo box and call procedure.
I'm still having problems. I get a Run-time error 1004: Unable to get the
OLEObjects property of the worksheet class. Below is what Ive programmed so far. 1.) In 2 worksheets, I created a command button to ask the user if they want to print. Below is the code in the individual sheets that does work. It invokes my userform combobox window. Private Sub CommandButton1_Click() UserForm.Show End Sub 2.) While in the worksheet, the userform pops up and the user selects from 3 report options in the combobox window. I included the below procedures into the Userform VBA project (module?). This was the code you gave me above. Not sure if I have used it correctly. I assigned a button from the Forms toolbar to the macro in I believe a module and then copied it to the userform VBA. Not sure exactly what I did. Option Explicit Sub ComboBox1_Change() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub 3.) Below is the code I used in the UserForm Initialize VBA in order to populate the combobox with 3 options. This appears to have worked. Private Sub UserForm_Initialize() ComboBox1.AddItem "Select Print Option" ComboBox1.AddItem "Charts 1-3, 1 page" ComboBox1.AddItem "Charts 1-5, 2 pages" End Sub "TAS" wrote: Most of what you wrote made sense. I'm attempting to use your 2nd suggestion so that it is less work ("If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name)." I'm confused about how to use a combobox from the control toolbox and use a button from the Forms toolbar. It seems like I'll end up with 2 control buttons on each worksheet. Below is the code I have at this point. Sub Print_Charts() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub Thanks. Terry "Dave Peterson" wrote: If you used a commandbutton from thecontrol toolbox toolbar, each one of those commandbuttons will have to call the common procedure. If you used a combobox from the Control toolbox toolbar on each sheet (not a dropdown from the Forms toolbar), and you gave each of the comboboxes the same name. The code behind the worksheet that the commandbutton uses would look like: Option Explicit Private Sub CommandButton1_Click() Call DoTheWork(Me.ComboBox1) End Sub And the code in a general module would look like: Option Explicit Sub DoTheWork(CBX As MSForms.ComboBox) With CBX 'just to show you that you got the right one 'MsgBox .Name & vbLf & .Parent.Name If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") .Parent.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub =========== If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name). Option Explicit Sub DoTheWork() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("Combobox1") With CBX.Object If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") ActiveSheet.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub TAS wrote: Details: I have over 100 identical worksheets with the same combo box user control scrollbar on each Excel worksheet to allow user to choose from one of 6 print options. I want to use a call procedure in each visual basic sheet so that I can maintain the detailed print option procedures in one module rather than in each sheet. I successfully experimented with a command button using the âœclickâ event control that called a procedure in another module. The only difference I can see is that my combo box uses the âœchangeâ event control with 6 different events based on the user selection. I canât figure out the syntax to make the call procedure work. Hopefully the lingo I used is accurate in describing my question. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual basic 6.5 question using combo box and call procedure.
I'm not sure why you're confused.
You'll end up with a combobox from the control toolbox toolbar and a button from the Forms toolbar on each sheet. You can have lots of objects on the sheet as well. You could insert a comment. You could insert a picture. Those won't affect either the combobox or button. But I am confused about why you changed the suggested code. I used a variable (CBX) to represent that combobox from the control toolbox. You can print a range directly without changing the sheet's .printarea property: Option Explicit Sub Print_Charts() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("Select Print Option") 'do nothing Case Is = LCase("Charts 1-3, 1 page") ActiveSheet.Range("AX7:BO56").PrintOut Case Is = LCase("Charts 1-5, 2 pages") ActiveSheet.Range("AX7:BO96").PrintOut End Select End If .ListIndex = 0 'first item in list End With End Sub TAS wrote: Most of what you wrote made sense. I'm attempting to use your 2nd suggestion so that it is less work ("If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name)." I'm confused about how to use a combobox from the control toolbox and use a button from the Forms toolbar. It seems like I'll end up with 2 control buttons on each worksheet. Below is the code I have at this point. Sub Print_Charts() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub Thanks. Terry "Dave Peterson" wrote: If you used a commandbutton from thecontrol toolbox toolbar, each one of those commandbuttons will have to call the common procedure. If you used a combobox from the Control toolbox toolbar on each sheet (not a dropdown from the Forms toolbar), and you gave each of the comboboxes the same name. The code behind the worksheet that the commandbutton uses would look like: Option Explicit Private Sub CommandButton1_Click() Call DoTheWork(Me.ComboBox1) End Sub And the code in a general module would look like: Option Explicit Sub DoTheWork(CBX As MSForms.ComboBox) With CBX 'just to show you that you got the right one 'MsgBox .Name & vbLf & .Parent.Name If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") .Parent.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub =========== If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name). Option Explicit Sub DoTheWork() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("Combobox1") With CBX.Object If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") ActiveSheet.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub TAS wrote: Details: I have over 100 identical worksheets with the same combo box user control scrollbar on each Excel worksheet to allow user to choose from one of 6 print options. I want to use a call procedure in each visual basic sheet so that I can maintain the detailed print option procedures in one module rather than in each sheet. I successfully experimented with a command button using the âœclickâ event control that called a procedure in another module. The only difference I can see is that my combo box uses the âœchangeâ event control with 6 different events based on the user selection. I canât figure out the syntax to make the call procedure work. Hopefully the lingo I used is accurate in describing my question. -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual basic 6.5 question using combo box and call procedure.
Try using the code I suggested in the previous post.
TAS wrote: I'm still having problems. I get a Run-time error 1004: Unable to get the OLEObjects property of the worksheet class. Below is what Ive programmed so far. 1.) In 2 worksheets, I created a command button to ask the user if they want to print. Below is the code in the individual sheets that does work. It invokes my userform combobox window. Private Sub CommandButton1_Click() UserForm.Show End Sub 2.) While in the worksheet, the userform pops up and the user selects from 3 report options in the combobox window. I included the below procedures into the Userform VBA project (module?). This was the code you gave me above. Not sure if I have used it correctly. I assigned a button from the Forms toolbar to the macro in I believe a module and then copied it to the userform VBA. Not sure exactly what I did. Option Explicit Sub ComboBox1_Change() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub 3.) Below is the code I used in the UserForm Initialize VBA in order to populate the combobox with 3 options. This appears to have worked. Private Sub UserForm_Initialize() ComboBox1.AddItem "Select Print Option" ComboBox1.AddItem "Charts 1-3, 1 page" ComboBox1.AddItem "Charts 1-5, 2 pages" End Sub "TAS" wrote: Most of what you wrote made sense. I'm attempting to use your 2nd suggestion so that it is less work ("If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name)." I'm confused about how to use a combobox from the control toolbox and use a button from the Forms toolbar. It seems like I'll end up with 2 control buttons on each worksheet. Below is the code I have at this point. Sub Print_Charts() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub Thanks. Terry "Dave Peterson" wrote: If you used a commandbutton from thecontrol toolbox toolbar, each one of those commandbuttons will have to call the common procedure. If you used a combobox from the Control toolbox toolbar on each sheet (not a dropdown from the Forms toolbar), and you gave each of the comboboxes the same name. The code behind the worksheet that the commandbutton uses would look like: Option Explicit Private Sub CommandButton1_Click() Call DoTheWork(Me.ComboBox1) End Sub And the code in a general module would look like: Option Explicit Sub DoTheWork(CBX As MSForms.ComboBox) With CBX 'just to show you that you got the right one 'MsgBox .Name & vbLf & .Parent.Name If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") .Parent.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub =========== If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name). Option Explicit Sub DoTheWork() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("Combobox1") With CBX.Object If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") ActiveSheet.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub TAS wrote: Details: I have over 100 identical worksheets with the same combo box user control scrollbar on each Excel worksheet to allow user to choose from one of 6 print options. I want to use a call procedure in each visual basic sheet so that I can maintain the detailed print option procedures in one module rather than in each sheet. I successfully experimented with a command button using the âœclickâ event control that called a procedure in another module. The only difference I can see is that my combo box uses the âœchangeâ event control with 6 different events based on the user selection. I canât figure out the syntax to make the call procedure work. Hopefully the lingo I used is accurate in describing my question. -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual basic 6.5 question using combo box and call procedure.
It looks like your followup post was delayed getting to the newsgroups.
Dave Peterson wrote: Try using the code I suggested in the previous post. TAS wrote: I'm still having problems. I get a Run-time error 1004: Unable to get the OLEObjects property of the worksheet class. Below is what Ive programmed so far. 1.) In 2 worksheets, I created a command button to ask the user if they want to print. Below is the code in the individual sheets that does work. It invokes my userform combobox window. Private Sub CommandButton1_Click() UserForm.Show End Sub 2.) While in the worksheet, the userform pops up and the user selects from 3 report options in the combobox window. I included the below procedures into the Userform VBA project (module?). This was the code you gave me above. Not sure if I have used it correctly. I assigned a button from the Forms toolbar to the macro in I believe a module and then copied it to the userform VBA. Not sure exactly what I did. Option Explicit Sub ComboBox1_Change() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub 3.) Below is the code I used in the UserForm Initialize VBA in order to populate the combobox with 3 options. This appears to have worked. Private Sub UserForm_Initialize() ComboBox1.AddItem "Select Print Option" ComboBox1.AddItem "Charts 1-3, 1 page" ComboBox1.AddItem "Charts 1-5, 2 pages" End Sub "TAS" wrote: Most of what you wrote made sense. I'm attempting to use your 2nd suggestion so that it is less work ("If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name)." I'm confused about how to use a combobox from the control toolbox and use a button from the Forms toolbar. It seems like I'll end up with 2 control buttons on each worksheet. Below is the code I have at this point. Sub Print_Charts() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub Thanks. Terry "Dave Peterson" wrote: If you used a commandbutton from thecontrol toolbox toolbar, each one of those commandbuttons will have to call the common procedure. If you used a combobox from the Control toolbox toolbar on each sheet (not a dropdown from the Forms toolbar), and you gave each of the comboboxes the same name. The code behind the worksheet that the commandbutton uses would look like: Option Explicit Private Sub CommandButton1_Click() Call DoTheWork(Me.ComboBox1) End Sub And the code in a general module would look like: Option Explicit Sub DoTheWork(CBX As MSForms.ComboBox) With CBX 'just to show you that you got the right one 'MsgBox .Name & vbLf & .Parent.Name If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") .Parent.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub =========== If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name). Option Explicit Sub DoTheWork() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("Combobox1") With CBX.Object If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") ActiveSheet.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub TAS wrote: Details: I have over 100 identical worksheets with the same combo box user control scrollbar on each Excel worksheet to allow user to choose from one of 6 print options. I want to use a call procedure in each visual basic sheet so that I can maintain the detailed print option procedures in one module rather than in each sheet. I successfully experimented with a command button using the âœclickâ event control that called a procedure in another module. The only difference I can see is that my combo box uses the âœchangeâ event control with 6 different events based on the user selection. I canât figure out the syntax to make the call procedure work. Hopefully the lingo I used is accurate in describing my question. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual basic 6.5 question using combo box and call procedure.
A belated thanks for the help at the end of Feb. I got super busy wth work
and personal issues so I forgot to respond. The code did work by the way. "Dave Peterson" wrote: Try using the code I suggested in the previous post. TAS wrote: I'm still having problems. I get a âœRun-time error â˜1004â: Unable to get the OLEObjects property of the worksheet class.â Below is what Iâve programmed so far. 1.) In 2 worksheets, I created a command button to ask the user if they want to print. Below is the code in the individual sheets that does work. It invokes my userform combobox window. Private Sub CommandButton1_Click() UserForm.Show End Sub 2.) While in the worksheet, the userform pops up and the user selects from 3 report options in the combobox window. I included the below procedures into the âœUserformâ VBA project (module?). This was the code you gave me above. Not sure if I have used it correctly. I assigned a button from the Forms toolbar to the macro in I believe a module and then copied it to the userform VBA. Not sure exactly what I did. Option Explicit Sub ComboBox1_Change() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub 3.) Below is the code I used in the âœUserForm Initializeâ VBA in order to populate the combobox with 3 options. This appears to have worked. Private Sub UserForm_Initialize() ComboBox1.AddItem "Select Print Option" ComboBox1.AddItem "Charts 1-3, 1 page" ComboBox1.AddItem "Charts 1-5, 2 pages" End Sub "TAS" wrote: Most of what you wrote made sense. I'm attempting to use your 2nd suggestion so that it is less work ("If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name)." I'm confused about how to use a combobox from the control toolbox and use a button from the Forms toolbar. It seems like I'll end up with 2 control buttons on each worksheet. Below is the code I have at this point. Sub Print_Charts() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub Thanks. Terry "Dave Peterson" wrote: If you used a commandbutton from thecontrol toolbox toolbar, each one of those commandbuttons will have to call the common procedure. If you used a combobox from the Control toolbox toolbar on each sheet (not a dropdown from the Forms toolbar), and you gave each of the comboboxes the same name. The code behind the worksheet that the commandbutton uses would look like: Option Explicit Private Sub CommandButton1_Click() Call DoTheWork(Me.ComboBox1) End Sub And the code in a general module would look like: Option Explicit Sub DoTheWork(CBX As MSForms.ComboBox) With CBX 'just to show you that you got the right one 'MsgBox .Name & vbLf & .Parent.Name If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") .Parent.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub =========== If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name). Option Explicit Sub DoTheWork() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("Combobox1") With CBX.Object If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") ActiveSheet.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub TAS wrote: Details: I have over 100 identical worksheets with the same combo box user control scrollbar on each Excel worksheet to allow user to choose from one of 6 print options. I want to use a call procedure in each visual basic sheet so that I can maintain the detailed print option procedures in one module rather than in each sheet. I successfully experimented with a command button using the ââ¬Åclickââ¬Â event control that called a procedure in another module. The only difference I can see is that my combo box uses the ââ¬Åchangeââ¬Â event control with 6 different events based on the user selection. I canââ¬â¢t figure out the syntax to make the call procedure work. Hopefully the lingo I used is accurate in describing my question. -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual basic 6.5 question using combo box and call procedure.
Glad it worked for you.
TAS wrote: A belated thanks for the help at the end of Feb. I got super busy wth work and personal issues so I forgot to respond. The code did work by the way. "Dave Peterson" wrote: Try using the code I suggested in the previous post. TAS wrote: I'm still having problems. I get a âœRun-time error â˜1004â: Unable to get the OLEObjects property of the worksheet class.â Below is what Iâve programmed so far. 1.) In 2 worksheets, I created a command button to ask the user if they want to print. Below is the code in the individual sheets that does work. It invokes my userform combobox window. Private Sub CommandButton1_Click() UserForm.Show End Sub 2.) While in the worksheet, the userform pops up and the user selects from 3 report options in the combobox window. I included the below procedures into the âœUserformâ VBA project (module?). This was the code you gave me above. Not sure if I have used it correctly. I assigned a button from the Forms toolbar to the macro in I believe a module and then copied it to the userform VBA. Not sure exactly what I did. Option Explicit Sub ComboBox1_Change() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub 3.) Below is the code I used in the âœUserForm Initializeâ VBA in order to populate the combobox with 3 options. This appears to have worked. Private Sub UserForm_Initialize() ComboBox1.AddItem "Select Print Option" ComboBox1.AddItem "Charts 1-3, 1 page" ComboBox1.AddItem "Charts 1-5, 2 pages" End Sub "TAS" wrote: Most of what you wrote made sense. I'm attempting to use your 2nd suggestion so that it is less work ("If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name)." I'm confused about how to use a combobox from the control toolbox and use a button from the Forms toolbar. It seems like I'll end up with 2 control buttons on each worksheet. Below is the code I have at this point. Sub Print_Charts() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("ComboBox1") With CBX.Object ' If ComboBox1.Value = "Select Print Option" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" ElseIf ComboBox1.Value = "Charts 1-3, 1 page" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$56" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select ElseIf ComboBox1.Value = "Charts 1-5, 2 pages" Then ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$96" ActiveWindow.SelectedSheets.PrintOut ActiveSheet.PageSetup.PrintArea = "$AX$7:$BO$194" ComboBox1.Value = "Select Print Option" Range("A2").Select End If End With End Sub Thanks. Terry "Dave Peterson" wrote: If you used a commandbutton from thecontrol toolbox toolbar, each one of those commandbuttons will have to call the common procedure. If you used a combobox from the Control toolbox toolbar on each sheet (not a dropdown from the Forms toolbar), and you gave each of the comboboxes the same name. The code behind the worksheet that the commandbutton uses would look like: Option Explicit Private Sub CommandButton1_Click() Call DoTheWork(Me.ComboBox1) End Sub And the code in a general module would look like: Option Explicit Sub DoTheWork(CBX As MSForms.ComboBox) With CBX 'just to show you that you got the right one 'MsgBox .Name & vbLf & .Parent.Name If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") .Parent.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub =========== If you used a button from the Forms toolbar, you could drop the code from each worksheet module and just assign the same macro to each button on each sheet (less work???). This still uses a combobox from the control toolbox and the name for each of them is "Combobox1" (yep, all the same name). Option Explicit Sub DoTheWork() Dim CBX As OLEObject Set CBX = ActiveSheet.OLEObjects("Combobox1") With CBX.Object If .ListIndex < 0 Then Beep 'nothing selected Else Select Case LCase(.Value) Case Is = LCase("partOne") ActiveSheet.Range("A1:b3").PrintOut preview:=True End Select End If End With End Sub TAS wrote: Details: I have over 100 identical worksheets with the same combo box user control scrollbar on each Excel worksheet to allow user to choose from one of 6 print options. I want to use a call procedure in each visual basic sheet so that I can maintain the detailed print option procedures in one module rather than in each sheet. I successfully experimented with a command button using the ââ¬Åclickââ¬Â event control that called a procedure in another module. The only difference I can see is that my combo box uses the ââ¬Åchangeââ¬Â event control with 6 different events based on the user selection. I canââ¬â¢t figure out the syntax to make the call procedure work. Hopefully the lingo I used is accurate in describing my question. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Call a Visual Basic Function with VLookup | Excel Worksheet Functions | |||
| Visual Basic Question | Excel Worksheet Functions | |||
| visual basic coding question | Excel Discussion (Misc queries) | |||
| Visual Basic Question | Excel Discussion (Misc queries) | |||
| changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
Linear Mode
