|
Get Value of ComboBox in Menu
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 |
Get Value of ComboBox in Menu
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 "RyanH" wrote in message ... |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 |
Get Value of ComboBox in Menu
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 "RyanH" wrote in message ... |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 |
Get Value of ComboBox in Menu
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 "RyanH" wrote in message ... | 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 | "RyanH" wrote in message | ... | |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 | | |
Get Value of ComboBox in Menu
Thanks for the tips! Here is what I used and it works great!
ActiveWindow.Zoom = Val(CommandBars.ActionControl.Text) -- Cheers, Ryan "Jim Rech" wrote: 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 "RyanH" wrote in message ... | 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 | "RyanH" wrote in message | ... | |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 | | |
| All times are GMT +1. The time now is 05:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com