Visual basic 6.5 question using combo box and call procedure.

[TAS]

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]

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]

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

 

[TAS]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

Try using the code I suggested in the previous post.

 

[Dave Peterson]

It looks like your followup post was delayed getting to the newsgroups.

 

[TAS]

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]

Glad it worked for you.

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.