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