Adding label for msoDropdown control type

[talytech]

Hello,

Is there anyway to position the caption for the msoDropdown control type on a custom command bar?

My Add-Ins include a msoControlDropdown but the caption for the dropdown is like a tooltip. I would like for it to be positioned to the left of the dropdown control like a label.

Is that possible or is there another solution that can display a label next to the dropdown?


'Let's create the menubarDim sht As Worksheet
Dim MyBar As CommandBar
Dim MyButton, MyButton2 As CommandBarButton
Dim MyList As CommandBarComboBox


On Error Resume Next
Application.CommandBars("Sheet Navigate").Delete 'If the command bar was created before, it is deleted.
On Error GoTo 0


Set MyBar = CommandBars.Add("Sheet Navigate", , False, True)
With MyBar


Set MyList = .Controls.Add(msoControlDropdown) 'Drop-down list is being created.
With MyList

.AddItem "One"
.AddItem "Two"
.AddItem "Three"
.Caption = "my dropdown"
.ListIndex = 1


'.TooltipText = "Sheet Navigate"
.OnAction = "Sheet_Navigate" 'Macro name that triggered by drop-down list
End With


.Protection = msoBarNoCustomize
.Position = msoBarTop
.Visible = True
End With

[georgiboy]

Maybe the below will help:

'Let's create the menubarDim sht As Worksheet
Dim MyBar As CommandBar
Dim MyButton, MyButton2 As CommandBarButton
Dim MyList As CommandBarComboBox


On Error Resume Next
Application.CommandBars("Sheet Navigate").Delete 'If the command bar was created before, it is deleted.
On Error GoTo 0

Set MyBar = CommandBars.Add("Sheet Navigate", , False, True)
With MyBar
With .Controls.Add(Type:=msoControlButton)
.Caption = "Here is a label"
.Style = msoButtonCaption
End With
Set MyList = .Controls.Add(msoControlDropdown) 'Drop-down list is being created.
With MyList
.AddItem "One"
.AddItem "Two"
.AddItem "Three"
.Caption = "my dropdown"
.ListIndex = 1
'.TooltipText = "Sheet Navigate"
.OnAction = "Sheet_Navigate" 'Macro name that triggered by drop-down list
End With
.Protection = msoBarNoCustomize
.Position = msoBarTop
.Visible = True
End With

[talytech]

Great! Thank you!!! That worked perfectly.

[talytech]

Oh wow .. now I'm in tears.

After getting my menu bar to work, I've closed out the spreadsheet.

Once I opened it again, it throws the following error: Method 'Add' of object 'CommandBars' failed.

I click ok, and the message goes away; however my Add-Ins menu is not showing up.

OMG What happened?? Please help me :(: pray2:

[georgiboy]

Do you have the code inside a Workbook_Open sub?

[talytech]

No.. its a Public Function. But I am getting the error for existing Excel.xslm files that used to work.

They each are failing on the following line of code.
Appliation.Commandbars("Worksheet Menu Bar").Controls.Add(msoControlPopup)

[georgiboy]

I thought you were working with 'msoControlDropdown'?

[talytech]

Yes.. the initial issue I had was trying to add label to the msoControlDropdow and your solution worked fine.

msoContrlDropdown is part of a custom menu bar that has other controls... it also includes a popup control.

When I closed out the spreadsheet and re-opened, it is when I received the error.

I continued to try and troubleshoot but it still gave the error.

I tried opening other Excel workbooks that have custom command bar that I created just the same and those Workbooks are no longer displaying the "Add-Ins" menu and also immediately gives the error upon opening the workbook.

I have no idea what happened to my Excel Application.

Could it be that I've corrupted something?

[georgiboy]

Effectively all you have done is added an empty button to your dropdown menu, i can't see how that would corrupt anything to be honest. I know that is not a lot of help but it is my thinking.

Maybe you would be able to share a file that displays the error, obviously removing all sensitive data OR creating a file that displays the error and upoading it here?

[talytech]

so .. I never figured out the issue with the error but I've moved on.

Would you know how to return the value in the dropdown list when selected?

[Aussiebear]

so .. I never figured out the issue with the error but I've moved on.

Are we to assume that you are finished with the intent of the thread then? Is that because you are unable to post a sample workbook or don't want to?

[talytech]

Hello Aussiebear.. no I never figured out my issue; however I did not need to send a sample workbook because I already confirmed that it is not an issue with the code rather it is related to my MS Office Excel. I've sent the file to others on the job and they had no issues at all. I'm continuing to work with my file outside on my local machine which is not giving me the issue that I have on my remote machine for my job.

The initial thread is pertaining to the msoControlDropdown. With @georgiboy's help, I successfully added the label. But I am having trouble trying to return the selected value from the dropdown.

Do I need to end this thread and start a new one? Please advise.

[georgiboy]

Move the below line out of the sub and place it above the sub:

Dim MyList As CommandBarComboBox

See below:

Option Explicit

Dim MyList As CommandBarComboBox

Sub test()
'Let's create the menubarDim sht As Worksheet
Dim MyBar As CommandBar
Dim MyButton, MyButton2 As CommandBarButton

On Error Resume Next
Application.CommandBars("Sheet Navigate").Delete 'If the command bar was created before, it is deleted.
On Error GoTo 0

Set MyBar = CommandBars.Add("Sheet Navigate", , False, True)
With MyBar
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button that is not a button"
.Style = msoButtonCaption
End With

Set MyList = .Controls.Add(msoControlDropdown) 'Drop-down list is being created.
With MyList
.AddItem "One"
.AddItem "Two"
.AddItem "Three"
.OnAction = "Sheet_Navigate" 'Macro name that triggered by drop-down list
.ListIndex = 1
End With
.Protection = msoBarNoCustomize
.Position = msoBarTop
.Visible = True
End With
End Sub

Sub Sheet_Navigate()
Debug.Print MyList.List(MyList.ListIndex) ' value from selected option
Debug.Print MyList.ListIndex ' selected list index
End Sub

[talytech]

Yesss!!! Perfection .. thank you so much georgiboy.. You saved me..