Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename and use toolbar menu name by user entry
Ok this my be hard to discribe but here goes.
I would like to create a customised menu toolbar with a menu drop down list. I would like a menu under this drop down list to show a 6 digit number as it's name. When the user clicks on this number a macro is run that asks the user for a new 6 digit number and displays this as the new menu name. I would like this new 6 digit name to be store in a public variable for other macros to use. I would like this menu button in a toolbar menu not in a excel file cell drop down list. Is this possible? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename and use toolbar menu name by user entry
This may get you started.
It actually doesn't store the number in a public variable--I figured it would be just as easy to read what the dropdown showed and use that. 'This stuff builds the toolbar and handles changes Option Explicit Public Const myToolBarName As String = "myNumbers" Sub auto_close() On Error Resume Next Application.CommandBars(myToolBarName).Delete On Error GoTo 0 End Sub Sub auto_open() Dim cb As CommandBar Dim Ctrl As CommandBarControl Dim wks As Worksheet On Error Resume Next Application.CommandBars(myToolBarName).Delete On Error GoTo 0 Set cb = Application.CommandBars.Add(Name:="myNumbers", temporary:=True) With cb .Position = msoBarTop .Visible = True Set Ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True) With Ctrl .Width = 75 .OnAction = "'" & ThisWorkbook.Name & "'!changetheNumber" End With End With End Sub Sub ChangeTheNumber() Dim myNumber As Long Dim ErrFound As Boolean Dim MatchFound As Boolean Dim iCtr As Long With Application.CommandBars.ActionControl If .ListIndex = 0 Then ErrFound = False If IsNumeric(.Text) Then myNumber = CLng(.Text) If myNumber < 100000 _ And myNumber 0 Then ErrFound = False 'check for existing value MatchFound = False For iCtr = 1 To .ListCount If myNumber = CLng(.List(iCtr)) Then 'found a match MatchFound = True .Text = .List(iCtr) Exit For End If Next iCtr End If End If If MatchFound Then 'do nothing Else If ErrFound = False Then .AddItem Format(myNumber, "000000") .Text = Format(myNumber, "000000") Else .Text = "" Beep End If End If End If End With End Sub 'and to show how to pick up that displayed number. Sub testme() Dim cb As CommandBar Dim myNumber As Long Set cb = Nothing On Error Resume Next Set cb = Application.CommandBars(myToolBarName) On Error GoTo 0 If cb Is Nothing Then MsgBox "the commandbar is gone!" Exit Sub End If If IsNumeric(cb.Controls(1).Text) = False Then MsgBox "nothing chosen" myNumber = 0 Else myNumber = CLng(cb.Controls(1).Text) End If MsgBox myNumber End Sub mcphc wrote: Ok this my be hard to discribe but here goes. I would like to create a customised menu toolbar with a menu drop down list. I would like a menu under this drop down list to show a 6 digit number as it's name. When the user clicks on this number a macro is run that asks the user for a new 6 digit number and displays this as the new menu name. I would like this new 6 digit name to be store in a public variable for other macros to use. I would like this menu button in a toolbar menu not in a excel file cell drop down list. Is this possible? Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename and use toolbar menu name by user entry
And change this line:
Set cb = Application.CommandBars.Add(Name:="myNumbers", temporary:=True) to Set cb = Application.CommandBars.Add(Name:=myToolBarName, temporary:=True) I wanted to make sure you only had to change the name of the toolbar in one spot--and I missed one. Dave Peterson wrote: This may get you started. It actually doesn't store the number in a public variable--I figured it would be just as easy to read what the dropdown showed and use that. 'This stuff builds the toolbar and handles changes Option Explicit Public Const myToolBarName As String = "myNumbers" Sub auto_close() On Error Resume Next Application.CommandBars(myToolBarName).Delete On Error GoTo 0 End Sub Sub auto_open() Dim cb As CommandBar Dim Ctrl As CommandBarControl Dim wks As Worksheet On Error Resume Next Application.CommandBars(myToolBarName).Delete On Error GoTo 0 Set cb = Application.CommandBars.Add(Name:="myNumbers", temporary:=True) With cb .Position = msoBarTop .Visible = True Set Ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True) With Ctrl .Width = 75 .OnAction = "'" & ThisWorkbook.Name & "'!changetheNumber" End With End With End Sub Sub ChangeTheNumber() Dim myNumber As Long Dim ErrFound As Boolean Dim MatchFound As Boolean Dim iCtr As Long With Application.CommandBars.ActionControl If .ListIndex = 0 Then ErrFound = False If IsNumeric(.Text) Then myNumber = CLng(.Text) If myNumber < 100000 _ And myNumber 0 Then ErrFound = False 'check for existing value MatchFound = False For iCtr = 1 To .ListCount If myNumber = CLng(.List(iCtr)) Then 'found a match MatchFound = True .Text = .List(iCtr) Exit For End If Next iCtr End If End If If MatchFound Then 'do nothing Else If ErrFound = False Then .AddItem Format(myNumber, "000000") .Text = Format(myNumber, "000000") Else .Text = "" Beep End If End If End If End With End Sub 'and to show how to pick up that displayed number. Sub testme() Dim cb As CommandBar Dim myNumber As Long Set cb = Nothing On Error Resume Next Set cb = Application.CommandBars(myToolBarName) On Error GoTo 0 If cb Is Nothing Then MsgBox "the commandbar is gone!" Exit Sub End If If IsNumeric(cb.Controls(1).Text) = False Then MsgBox "nothing chosen" myNumber = 0 Else myNumber = CLng(cb.Controls(1).Text) End If MsgBox myNumber End Sub mcphc wrote: Ok this my be hard to discribe but here goes. I would like to create a customised menu toolbar with a menu drop down list. I would like a menu under this drop down list to show a 6 digit number as it's name. When the user clicks on this number a macro is run that asks the user for a new 6 digit number and displays this as the new menu name. I would like this new 6 digit name to be store in a public variable for other macros to use. I would like this menu button in a toolbar menu not in a excel file cell drop down list. Is this possible? Thanks -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename and use toolbar menu name by user entry
Thanks Dave, managed to figure it out
"Dave Peterson" wrote: And change this line: Set cb = Application.CommandBars.Add(Name:="myNumbers", temporary:=True) to Set cb = Application.CommandBars.Add(Name:=myToolBarName, temporary:=True) I wanted to make sure you only had to change the name of the toolbar in one spot--and I missed one. Dave Peterson wrote: This may get you started. It actually doesn't store the number in a public variable--I figured it would be just as easy to read what the dropdown showed and use that. 'This stuff builds the toolbar and handles changes Option Explicit Public Const myToolBarName As String = "myNumbers" Sub auto_close() On Error Resume Next Application.CommandBars(myToolBarName).Delete On Error GoTo 0 End Sub Sub auto_open() Dim cb As CommandBar Dim Ctrl As CommandBarControl Dim wks As Worksheet On Error Resume Next Application.CommandBars(myToolBarName).Delete On Error GoTo 0 Set cb = Application.CommandBars.Add(Name:="myNumbers", temporary:=True) With cb .Position = msoBarTop .Visible = True Set Ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True) With Ctrl .Width = 75 .OnAction = "'" & ThisWorkbook.Name & "'!changetheNumber" End With End With End Sub Sub ChangeTheNumber() Dim myNumber As Long Dim ErrFound As Boolean Dim MatchFound As Boolean Dim iCtr As Long With Application.CommandBars.ActionControl If .ListIndex = 0 Then ErrFound = False If IsNumeric(.Text) Then myNumber = CLng(.Text) If myNumber < 100000 _ And myNumber 0 Then ErrFound = False 'check for existing value MatchFound = False For iCtr = 1 To .ListCount If myNumber = CLng(.List(iCtr)) Then 'found a match MatchFound = True .Text = .List(iCtr) Exit For End If Next iCtr End If End If If MatchFound Then 'do nothing Else If ErrFound = False Then .AddItem Format(myNumber, "000000") .Text = Format(myNumber, "000000") Else .Text = "" Beep End If End If End If End With End Sub 'and to show how to pick up that displayed number. Sub testme() Dim cb As CommandBar Dim myNumber As Long Set cb = Nothing On Error Resume Next Set cb = Application.CommandBars(myToolBarName) On Error GoTo 0 If cb Is Nothing Then MsgBox "the commandbar is gone!" Exit Sub End If If IsNumeric(cb.Controls(1).Text) = False Then MsgBox "nothing chosen" myNumber = 0 Else myNumber = CLng(cb.Controls(1).Text) End If MsgBox myNumber End Sub mcphc wrote: Ok this my be hard to discribe but here goes. I would like to create a customised menu toolbar with a menu drop down list. I would like a menu under this drop down list to show a 6 digit number as it's name. When the user clicks on this number a macro is run that asks the user for a new 6 digit number and displays this as the new menu name. I would like this new 6 digit name to be store in a public variable for other macros to use. I would like this menu button in a toolbar menu not in a excel file cell drop down list. Is this possible? Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
keyboard shortcut to pull up sheet menu(copy,delete,rename,etc)? | Excel Discussion (Misc queries) | |||
Toolbar/Menu Bar | Excel Discussion (Misc queries) | |||
Worksheet Menu Toolbar | Excel Programming | |||
prevent user to rename sheet | Excel Programming | |||
Is there a way to ReName a Sheet Using a Drop Down Menu? | Excel Programming |