HELP - How to capture value of msoControlEdit on commandbar

[Scott Brogan]

HELP - Thank you in advance for your help.

I have what I think is a simple requirement but I've run out of ideas.
1) create a simple edit box and a button on a toolbar in office (excel, word, etc.,)
2) enter a value into the edit box
3) click on the button
4) retrieve the value from the edit box
NOTE: I've noticed that the value in the edit box does not persist unless I enter the text and hit "TAB" or "ENTER" to 'store' the value into the box. When I click on a another control, the value immediately disappears. I've tried to capture the data using with events as described in the link below but only a few events are available (and the value in the edit box disappears before I can capture it).
http://msdn.microsoft.com/library/d...ore/html/deovrworkingwithcommandbarevents.asp

Can anybody please help me capture the value from the edit box when focus is lost to another control in the app? I want the behavior of the edit box and button to be similar to a simple edit box and button on a form (i.e., persist the data entry).


Here is the sample code I've been experimenting with. I've tried calling two different functions using .onaction (InvokeMessageBox and InvokeMessageBox1 respectively) but they only capture the empty edit box.


Sub CreateOfficeBar()

Dim cbrCommandBar As CommandBar
Dim cbcCommandBarButton As CommandBarButton
Dim cbcCommandBarTextBox As CommandBarComboBox

On Error Resume Next
Application.CommandBars("Office Bar Sample").Delete

Set cbrCommandBar = Application.CommandBars.Add
cbrCommandBar.Name = "Office Bar Sample"

With cbrCommandBar.Controls
Set cbcCommandBarTextBox = .Add(msoControlEdit)
With cbcCommandBarTextBox
.Tag = "EditBox"
End With

Set cbcCommandBarButton = .Add(msoControlButton)
With cbcCommandBarButton
.Caption = "Click Me"
.Style = msoButtonCaption
.OnAction = "InvokeMessageBox"
.Tag = cbcCommandBarTextBox.Text
End With
End With

cbrCommandBar.Visible = True

End Sub

Function InvokeMessageBox()
Dim MessageBoxValue As String
Dim ctl As CommandBarControl

For Each ctl In CommandBars("Office Bar Sample").Controls
If ctl.Tag = "EditBox" Then
MessageBoxValue = Trim(ctl.Text)
End If
Next ctl

MsgBox MessageBoxValue


End Function

Function InvokeMessageBox1()
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars.ActionControl

MessageBoxValue = ctl.Tag

MsgBox MessageBoxValue

End Function

Thank you very much!

Scott

 

[Tom Winter]

HELP - Thank you in advance for your help.

I have what I think is a simple requirement but I've run out of ideas.
1) create a simple edit box and a button on a toolbar in office (excel, word, etc.,)
2) enter a value into the edit box
3) click on the button
4) retrieve the value from the edit box
NOTE: I've noticed that the value in the edit box does not persist unless

I enter the text and hit "TAB" or "ENTER" to 'store' the value into the box.
When I click on a another control, the value immediately disappears. I've
tried to capture the data using with events as described in the link below
but only a few events are available (and the value in the edit box
disappears before I can capture it).

<snip>

This is the way that edit controls in toolbars work. Try this in any office
application. In Word, start typing in the Font Name edit box on the toolbar,
then click back in the document (or elsewhere) without hitting enter. It
reverts to its former value. That's how Microsoft designed them and you
probably can't change that behavior. One thing to think of is that since
this is how all OTHER edit boxes on toolbars will work, your end-users will
probably expect yours to work the same way. Why change the normal behavior
of things?

-Tom

 

[Sam Hobbs]

This is the way that edit controls in toolbars work. Try this in any office
application. In Word, start typing in the Font Name edit box on the toolbar,
then click back in the document (or elsewhere) without hitting enter. It
reverts to its former value.

Perhaps I don't know what you mean by "Font Name edit box", but if I do,
then it is part of a combobox, so it would behave differently from
individual edit controls. When typing in the "Font Name edit box", does a
list of fonts appear (usually underneath)?

I am not familiar with Office toolbars as a programmer, but it seems strange
to me that an individual edit control works in the manner described, even
when in an Office toolbar.

 

[Scott Brogan]

Thank you for the replies. I agree this is 'normal' behavior for an office toolbar but I thought there might be a work-around. I suppose I can instruct my users to hit "enter" or "tab" to initiate the action..

Scott

 

[Sam Hobbs]

Thank you for the replies. I agree this is 'normal' behavior for an

office toolbar but I thought there might be a work-around. I suppose I can
instruct my users to hit "enter" or "tab" to initiate the action...


No, I am saying that if I understand what is being described, then it is not
normal. Perhaps I am wrong and there is something strange about Office
toolbars, but I agree that an edit control should not work that way. What I
was saying in my reply was that perhaps a combobox control is being confused
as an edit control.