Get Value of ComboBox in Menu

[RyanH]

I have a custom menu tab that contains a submenu item which contains a
combobox. I would like to set the zoom property of several worksheets to the
value of the combobox when it is changed.

1.) How can I get the value of the combobox? I want to call this Sub when
the combox is changed.

Public Sub ChangeDeptViews()

SubName = "ChangeDeptViews"

' make sure Global Schedule is the active sheet
If Not IsGlobalAvailable() Then
Exit Sub
End If

Application.ScreenUpdating = False

' store current sheet
Set wksCurrentSheet = ActiveSheet

Sheets(Array("Engineering", "Graph Prod", "Metal Fab", "Alum Ext", _
"Custom Fab", "Electrical", "Ch Ltrs", "Foam Fab", _
"Metal Paint", "Thermo", "Tri Graphics", "Deco Faces", _
"Tri-Face", "LED", "Crating", "Service",
"Delivery")).Select
Sheets("Engineering").Activate
DOESN'T WORK>>> ActiveWindow.Zoom = CommandBars(1).Controls("Depts.
View").Value

' restore sheet that was active
wksCurrentSheet.Activate
Application.ScreenUpdating = False

End Sub

 

[Jim Rech]

I have a custom menu tab

Assuming you're talking about pre-Excel 2007 commandbars and not the ribbon
("tab" is a bit confusing), then all you have to do is assign a macro to the
combobox with its OnAction property and have that sub determine which item
was selected:

Sub MacroAssignedToCombobox()
MsgBox CommandBars.ActionControl.ListIndex
' or
MsgBox CommandBars.ActionControl.Text
End Sub


--
Jim
|I have a custom menu tab that contains a submenu item which contains a
| combobox. I would like to set the zoom property of several worksheets to
the
| value of the combobox when it is changed.
|
| 1.) How can I get the value of the combobox? I want to call this Sub
when
| the combox is changed.
|
| Public Sub ChangeDeptViews()
|
| SubName = "ChangeDeptViews"
|
| ' make sure Global Schedule is the active sheet
| If Not IsGlobalAvailable() Then
| Exit Sub
| End If
|
| Application.ScreenUpdating = False
|
| ' store current sheet
| Set wksCurrentSheet = ActiveSheet
|
| Sheets(Array("Engineering", "Graph Prod", "Metal Fab", "Alum Ext", _
| "Custom Fab", "Electrical", "Ch Ltrs", "Foam Fab", _
| "Metal Paint", "Thermo", "Tri Graphics", "Deco Faces",
_
| "Tri-Face", "LED", "Crating", "Service",
| "Delivery")).Select
| Sheets("Engineering").Activate
| DOESN'T WORK>>> ActiveWindow.Zoom = CommandBars(1).Controls("Depts.
| View").Value
|
| ' restore sheet that was active
| wksCurrentSheet.Activate
| Application.ScreenUpdating = False
|
| End Sub
|
| --
| Cheers,
| Ryan

 

[RyanH]

Yes, this menu tab is in 2003. But adding the line you suggested below does
not work. When I type the line the Auto List Members does not contain the
..Text property.

Public Sub ChangeDeptViews()

SubName = "ChangeDeptViews"

Dim wksCurrentSheet As Worksheet

' make sure Global Schedule is the active sheet
If Not IsGlobalAvailable() Then
Exit Sub
End If

SubName = "ChangeDeptViews"

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

' store current sheet
Set wksCurrentSheet = ActiveSheet

Sheets(Array("Engineering", "Graph Prod", "Metal Fab", "Alum Ext", _
"Custom Fab", "Electrical", "Ch Ltrs", "Foam Fab", _
"Metal Paint", "Thermo", "Tri Graphics", "Deco Faces", _
"Tri-Face", "LED", "Crating", "Service",
"Delivery")).Select
Sheets("Engineering").Activate
ERROR>>> ActiveWindow.Zoom = CommandBars.ActionControl.Text

' restore sheet that was active
wksCurrentSheet.Activate

With Application
.ScreenUpdating = False
.EnableEvents = True
End With

End Sub

 

[Jim Rech]

The code I suggested was from a working example so it's good. Don't worry
about the "auto list" as what shows up depends on how the object is
declared. Make sure that the .Text you get is exactly what the .Zoom
property is looking for. Zoom wants a number, .Text returns a string. You
might have to convert it. Experiment.

--
Jim
| Yes, this menu tab is in 2003. But adding the line you suggested below
does
| not work. When I type the line the Auto List Members does not contain the
| .Text property.
|
| Public Sub ChangeDeptViews()
|
| SubName = "ChangeDeptViews"
|
| Dim wksCurrentSheet As Worksheet
|
| ' make sure Global Schedule is the active sheet
| If Not IsGlobalAvailable() Then
| Exit Sub
| End If
|
| SubName = "ChangeDeptViews"
|
| With Application
| .ScreenUpdating = False
| .EnableEvents = False
| End With
|
| ' store current sheet
| Set wksCurrentSheet = ActiveSheet
|
| Sheets(Array("Engineering", "Graph Prod", "Metal Fab", "Alum Ext", _
| "Custom Fab", "Electrical", "Ch Ltrs", "Foam Fab", _
| "Metal Paint", "Thermo", "Tri Graphics", "Deco Faces",
_
| "Tri-Face", "LED", "Crating", "Service",
| "Delivery")).Select
| Sheets("Engineering").Activate
| ERROR>>> ActiveWindow.Zoom = CommandBars.ActionControl.Text
|
| ' restore sheet that was active
| wksCurrentSheet.Activate
|
| With Application
| .ScreenUpdating = False
| .EnableEvents = True
| End With
|
| End Sub
| --
| Cheers,
| Ryan
|
|
| "Jim Rech" wrote:
|
| > >>I have a custom menu tab
| >
| > Assuming you're talking about pre-Excel 2007 commandbars and not the
ribbon
| > ("tab" is a bit confusing), then all you have to do is assign a macro to
the
| > combobox with its OnAction property and have that sub determine which
item
| > was selected:
| >
| > Sub MacroAssignedToCombobox()
| > MsgBox CommandBars.ActionControl.ListIndex
| > ' or
| > MsgBox CommandBars.ActionControl.Text
| > End Sub
| >
| >
| > --
| > Jim
| > | > |I have a custom menu tab that contains a submenu item which contains a
| > | combobox. I would like to set the zoom property of several worksheets
to
| > the
| > | value of the combobox when it is changed.
| > |
| > | 1.) How can I get the value of the combobox? I want to call this Sub
| > when
| > | the combox is changed.
| > |
| > | Public Sub ChangeDeptViews()
| > |
| > | SubName = "ChangeDeptViews"
| > |
| > | ' make sure Global Schedule is the active sheet
| > | If Not IsGlobalAvailable() Then
| > | Exit Sub
| > | End If
| > |
| > | Application.ScreenUpdating = False
| > |
| > | ' store current sheet
| > | Set wksCurrentSheet = ActiveSheet
| > |
| > | Sheets(Array("Engineering", "Graph Prod", "Metal Fab", "Alum Ext",
_
| > | "Custom Fab", "Electrical", "Ch Ltrs", "Foam Fab",
_
| > | "Metal Paint", "Thermo", "Tri Graphics", "Deco
Faces",
| > _
| > | "Tri-Face", "LED", "Crating", "Service",
| > | "Delivery")).Select
| > | Sheets("Engineering").Activate
| > | DOESN'T WORK>>> ActiveWindow.Zoom = CommandBars(1).Controls("Depts.
| > | View").Value
| > |
| > | ' restore sheet that was active
| > | wksCurrentSheet.Activate
| > | Application.ScreenUpdating = False
| > |
| > | End Sub
| > |
| > | --
| > | Cheers,
| > | Ryan
| >
| >

 

[RyanH]

Thanks for the tips! Here is what I used and it works great!

ActiveWindow.Zoom = Val(CommandBars.ActionControl.Text)