I'm working with Excel 2003, and I'm trying to determine if there is a
way to get the text in a VBE toolbar control. Specifically, I'm
referencing the Standard toolbar in VBE, control ID 3201 (or at least
I believe this is the right control). This control lists the line and
column for which the cursor is currently located within the code
window, e.g. "Ln 7, Col 30". I used the FindCtrlIdViaToolbarName
procedure below to determine this control ID. If you run the
procedure you'll notice that the control is added to the newly created
toolbar as a button with the caption of "Gauge" and does not look like
the control on the native VBE Standard toolbar. I set up the
GetCtrlText procedure to try and determine if I can use a property to
return the text within this control.

I'm led to believe that it may not be possible to get the text in this
control. As a result, I'm looking for some way to get the code line
for which the cursor, or some other text marker (e.g. "ThisCodeLine"),
is in.

What I mean by text marker is this:
1 | Sub TestCodeLine()
2 | Dim myText As String
3 | myText = "ThisCodeLine"
4 | End Sub
The code line would be 3 for the text marker "ThisCodeLine."

I don't have any experience with coding VBE, but given that there is a
".CodeModule.Lines(StartLine, Count)" construct, I'm led to believe
that there may be a property somewhere to return what I'm looking
for. I'm still digging around.

Thanks,

Matthew Herbert

Sub FindCtrlIdViaToolbarName()
Dim myBarName As String
Dim nativeTBarName As String
Dim nativeCBar As CommandBar
Dim nativeCtrl As CommandBarControl
Dim chgTooltip As Boolean
Dim i As Long
Dim cBar As CommandBar
Dim ctrl As CommandBarControl

nativeTBarName = "Standard"

myBarName = "ID via Toolbar Name"
For Each cBar In Application.VBE.CommandBars
If cBar.Name = myBarName Then
cBar.Delete
End If
Next

Set cBar = Application.VBE.CommandBars.Add(myBarName, msoBarFloating,
False, True)
cBar.Visible = True

chgTooltip = True
i = 0

For Each nativeCBar In Application.VBE.CommandBars
If nativeCBar.Name = nativeTBarName Then
For Each nativeCtrl In nativeCBar.Controls
i = i + 1
Debug.Print i; " | Native Name:"; nativeCtrl.TooltipText;
" | ID:"; nativeCtrl.ID
'some controls won't add and the TooltipText won't change
either
''so I added the On Error statement
On Error Resume Next
cBar.Controls.Add ID:=nativeCtrl.ID
If chgTooltip Then
cBar.Controls(i).TooltipText = nativeCtrl.ID
End If
Next
End If
Next

End Sub

Sub GetCtrlText()
Dim myID As Long
Dim myCtrl

myID = 3201
Set myCtrl = Application.VBE.CommandBars.FindControl(ID:=myID)
'Can't seem to find a property that will return the text in the
control
Debug.Print myCtrl.Caption

End Sub

Chip Pearson has some good info on his site. I'm sure you'll be able to find
something to help you here.

http://www.cpearson.com/excel/vbe.aspx
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



" wrote:

I'm working with Excel 2003, and I'm trying to determine if there is a
way to get the text in a VBE toolbar control. Specifically, I'm
referencing the Standard toolbar in VBE, control ID 3201 (or at least
I believe this is the right control). This control lists the line and
column for which the cursor is currently located within the code
window, e.g. "Ln 7, Col 30". I used the FindCtrlIdViaToolbarName
procedure below to determine this control ID. If you run the
procedure you'll notice that the control is added to the newly created
toolbar as a button with the caption of "Gauge" and does not look like
the control on the native VBE Standard toolbar. I set up the
GetCtrlText procedure to try and determine if I can use a property to
return the text within this control.

I'm led to believe that it may not be possible to get the text in this
control. As a result, I'm looking for some way to get the code line
for which the cursor, or some other text marker (e.g. "ThisCodeLine"),
is in.

What I mean by text marker is this:
1 | Sub TestCodeLine()
2 | Dim myText As String
3 | myText = "ThisCodeLine"
4 | End Sub
The code line would be 3 for the text marker "ThisCodeLine."

I don't have any experience with coding VBE, but given that there is a
".CodeModule.Lines(StartLine, Count)" construct, I'm led to believe
that there may be a property somewhere to return what I'm looking
for. I'm still digging around.

Thanks,

Matthew Herbert

Sub FindCtrlIdViaToolbarName()
Dim myBarName As String
Dim nativeTBarName As String
Dim nativeCBar As CommandBar
Dim nativeCtrl As CommandBarControl
Dim chgTooltip As Boolean
Dim i As Long
Dim cBar As CommandBar
Dim ctrl As CommandBarControl

nativeTBarName = "Standard"

myBarName = "ID via Toolbar Name"
For Each cBar In Application.VBE.CommandBars
If cBar.Name = myBarName Then
cBar.Delete
End If
Next

Set cBar = Application.VBE.CommandBars.Add(myBarName, msoBarFloating,
False, True)
cBar.Visible = True

chgTooltip = True
i = 0

For Each nativeCBar In Application.VBE.CommandBars
If nativeCBar.Name = nativeTBarName Then
For Each nativeCtrl In nativeCBar.Controls
i = i + 1
Debug.Print i; " | Native Name:"; nativeCtrl.TooltipText;
" | ID:"; nativeCtrl.ID
'some controls won't add and the TooltipText won't change
either
''so I added the On Error statement
On Error Resume Next
cBar.Controls.Add ID:=nativeCtrl.ID
If chgTooltip Then
cBar.Controls(i).TooltipText = nativeCtrl.ID
End If
Next
End If
Next

End Sub

Sub GetCtrlText()
Dim myID As Long
Dim myCtrl

myID = 3201
Set myCtrl = Application.VBE.CommandBars.FindControl(ID:=myID)
'Can't seem to find a property that will return the text in the
control
Debug.Print myCtrl.Caption

End Sub

On Feb 20, 1:33*pm, wrote:
On Feb 17, 3:41*pm, Barb Reinhardt





wrote:
Chip Pearson has some good info on his site. *I'm sure you'll be able to find
something to help you here.

http://www.cpearson.com/excel/vbe.aspx
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

" wrote:
I'm working with Excel 2003, and I'm trying to determine if there is a
way to get the text in a VBE toolbar control. *Specifically, I'm
referencing the Standard toolbar in VBE, control ID 3201 (or at least
I believe this is the right control). *This control lists the line and
column for which the cursor is currently located within the code
window, e.g. "Ln 7, Col 30". *I used the FindCtrlIdViaToolbarName
procedure below to determine this control ID. *If you run the
procedure you'll notice that the control is added to the newly created
toolbar as a button with the caption of "Gauge" and does not look like
the control on the native VBE Standard toolbar. *I set up the
GetCtrlText procedure to try and determine if I can use a property to
return the text within this control.

I'm led to believe that it may not be possible to get the text in this
control. *As a result, I'm looking for some way to get the code line
for which the cursor, or some other text marker (e.g. "ThisCodeLine"),
is in.

What I mean by text marker is this:
1 | Sub TestCodeLine()
2 | Dim myText As String
3 | myText = "ThisCodeLine"
4 | End Sub
The code line would be 3 for the text marker "ThisCodeLine."

I don't have any experience with coding VBE, but given that there is a
".CodeModule.Lines(StartLine, Count)" construct, I'm led to believe
that there may be a property somewhere to return what I'm looking
for. *I'm still digging around.

Thanks,

Matthew Herbert

Sub FindCtrlIdViaToolbarName()
Dim myBarName As String
Dim nativeTBarName As String
Dim nativeCBar As CommandBar
Dim nativeCtrl As CommandBarControl
Dim chgTooltip As Boolean
Dim i As Long
Dim cBar As CommandBar
Dim ctrl As CommandBarControl

nativeTBarName = "Standard"

myBarName = "ID via Toolbar Name"
For Each cBar In Application.VBE.CommandBars
* * If cBar.Name = myBarName Then
* * * * cBar.Delete
* * End If
Next

Set cBar = Application.VBE.CommandBars.Add(myBarName, msoBarFloating,
False, True)
cBar.Visible = True

chgTooltip = True
i = 0

For Each nativeCBar In Application.VBE.CommandBars
* * If nativeCBar.Name = nativeTBarName Then
* * * * For Each nativeCtrl In nativeCBar.Controls
* * * * * * i = i + 1
* * * * * * Debug.Print i; " | Native Name:"; nativeCtrl.TooltipText;
" | ID:"; nativeCtrl.ID
* * * * * * 'some controls won't add and the TooltipText won't change
either
* * * * * * ''so I added the On Error statement
* * * * * * On Error Resume Next
* * * * * * cBar.Controls.Add ID:=nativeCtrl.ID
* * * * * * If chgTooltip Then
* * * * * * * * cBar.Controls(i).TooltipText = nativeCtrl.ID
* * * * * * End If
* * * * Next
* * End If
Next

End Sub

Sub GetCtrlText()
Dim myID As Long
Dim myCtrl

myID = 3201
Set myCtrl = Application.VBE.CommandBars.FindControl(ID:=myID)
'Can't seem to find a property that will return the text in the
control
Debug.Print myCtrl.Caption

End Sub- Hide quoted text -

- Show quoted text -

Barb,

Thanks for pointing me to some of Chip's material. *The material will
be useful in getting to my objective; however, I'm still exploring the
code and feel I may need to access the text on the "Gauge" control. *I
know that creating a Class will not allow you to access container
events for UserForms controls (but the Class will allow you to access
non-container events for UserForm controls). *I'm trying to create a
way to access the text in Application.VBE.CommandBars.FindControl
(ID:=3201) to return the "Ln #". *It may be easiest to show why below..

Private Sub txtBox1_Exit() 'container event
myLine = 'extract the "Ln #" number for this specific line
myText = 'loop up from myLine until you find "Sub" or "Function" and
extract the "Sub/Function" line of text

'create a function "GetText" that uses Split, InStr, or InStrRev to
extract the control name from myText
myTextName = GetText(myText) 'this would return "txtBox1"

'rather than writing "Set myCtrl = Me.txtBox1" use the next line of
code
Set myCtrl = Me.Controls(myTextName)

'manipulate the control

End Sub

Creating something in this manner will allow me to use the code
systematically. *If I have multiple controls, it becomes a bit
cumbersome to change the "Set myCtrl = Me.txtBox1" line of code to
"Me.txtBox2" or "Me.txtBox3", etc. for each of the _Exit events (or
any container event for that matter).

I'm still experimenting and looking around.

Best,

Matt- Hide quoted text -

- Show quoted text -

Well, here is a post from myself with a solution.

Dim thisCtrl As Control
Dim lngCodeLn As Long
Dim lngStartLn As Long
Dim lngStartCol As Long
Dim lngEndLn As Long
Dim lngEndCol As Long
Dim strCtrlText As String
Dim codeMod As VBIDE.CodeModule

Private Sub txtBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Application.VBE.ActiveCodePane.GetSelection lngStartLn, lngStartCol,
lngEndLn, lngEndCol
lngCodeLn = lngStartLn
Set codeMod = ActiveWorkbook.VBProject.VBComponents
(Me.Name).CodeModule
strCtrlText = GetEventControlName(codeMod, lngCodeLn)

Set thisCtrl = Me.Controls(strCtrlText)

'manipulate the control

End Sub

Function GetEventControlName(VBCodeMod As VBIDE.CodeModule, lngLn As
Long)
Dim strProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind '0 is Sub or Function
Dim lngUnderscore As Long
Dim strCtrlName As String

strProcName = VBCodeMod.ProcOfLine(lngLn, ProcKind)

'get the first "_" starting from the right and moving to the left
lngUnderscore = InStrRev(strProcName, "_", , vbTextCompare)

'get the text to the left of the "_"
strCtrlName = Left(strProcName, lngUnderscore - 1)

GetEventControlName = strCtrlName

End Function

Best,

Matt

On Feb 20, 9:36*pm, wrote:
On Feb 20, 1:33*pm, wrote:





On Feb 17, 3:41*pm, Barb Reinhardt

wrote:
Chip Pearson has some good info on his site. *I'm sure you'll be able to find
something to help you here.

http://www.cpearson.com/excel/vbe.aspx
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

" wrote:
I'm working with Excel 2003, and I'm trying to determine if there is a
way to get the text in a VBE toolbar control. *Specifically, I'm
referencing the Standard toolbar in VBE, control ID 3201 (or at least
I believe this is the right control). *This control lists the line and
column for which the cursor is currently located within the code
window, e.g. "Ln 7, Col 30". *I used the FindCtrlIdViaToolbarName
procedure below to determine this control ID. *If you run the
procedure you'll notice that the control is added to the newly created
toolbar as a button with the caption of "Gauge" and does not look like
the control on the native VBE Standard toolbar. *I set up the
GetCtrlText procedure to try and determine if I can use a property to
return the text within this control.

I'm led to believe that it may not be possible to get the text in this
control. *As a result, I'm looking for some way to get the code line
for which the cursor, or some other text marker (e.g. "ThisCodeLine"),
is in.

What I mean by text marker is this:
1 | Sub TestCodeLine()
2 | Dim myText As String
3 | myText = "ThisCodeLine"
4 | End Sub
The code line would be 3 for the text marker "ThisCodeLine."

I don't have any experience with coding VBE, but given that there is a
".CodeModule.Lines(StartLine, Count)" construct, I'm led to believe
that there may be a property somewhere to return what I'm looking
for. *I'm still digging around.

Thanks,

Matthew Herbert

Sub FindCtrlIdViaToolbarName()
Dim myBarName As String
Dim nativeTBarName As String
Dim nativeCBar As CommandBar
Dim nativeCtrl As CommandBarControl
Dim chgTooltip As Boolean
Dim i As Long
Dim cBar As CommandBar
Dim ctrl As CommandBarControl

nativeTBarName = "Standard"

myBarName = "ID via Toolbar Name"
For Each cBar In Application.VBE.CommandBars
* * If cBar.Name = myBarName Then
* * * * cBar.Delete
* * End If
Next

Set cBar = Application.VBE.CommandBars.Add(myBarName, msoBarFloating,
False, True)
cBar.Visible = True

chgTooltip = True
i = 0

For Each nativeCBar In Application.VBE.CommandBars
* * If nativeCBar.Name = nativeTBarName Then
* * * * For Each nativeCtrl In nativeCBar.Controls
* * * * * * i = i + 1
* * * * * * Debug.Print i; " | Native Name:"; nativeCtrl.TooltipText;
" | ID:"; nativeCtrl.ID
* * * * * * 'some controls won't add and the TooltipText won't change
either
* * * * * * ''so I added the On Error statement
* * * * * * On Error Resume Next
* * * * * * cBar.Controls.Add ID:=nativeCtrl.ID
* * * * * * If chgTooltip Then
* * * * * * * * cBar.Controls(i).TooltipText = nativeCtrl.ID
* * * * * * End If
* * * * Next
* * End If
Next

End Sub

Sub GetCtrlText()
Dim myID As Long
Dim myCtrl

myID = 3201
Set myCtrl = Application.VBE.CommandBars.FindControl(ID:=myID)
'Can't seem to find a property that will return the text in the
control
Debug.Print myCtrl.Caption

End Sub- Hide quoted text -

- Show quoted text -

Barb,

Thanks for pointing me to some of Chip's material. *The material will
be useful in getting to my objective; however, I'm still exploring the
code and feel I may need to access the text on the "Gauge" control. *I
know that creating a Class will not allow you to access container
events for UserForms controls (but the Class will allow you to access
non-container events for UserForm controls). *I'm trying to create a
way to access the text in Application.VBE.CommandBars.FindControl
(ID:=3201) to return the "Ln #". *It may be easiest to show why below.

Private Sub txtBox1_Exit() 'container event
myLine = 'extract the "Ln #" number for this specific line
myText = 'loop up from myLine until you find "Sub" or "Function" and
extract the "Sub/Function" line of text

'create a function "GetText" that uses Split, InStr, or InStrRev to
extract the control name from myText
myTextName = GetText(myText) 'this would return "txtBox1"

'rather than writing "Set myCtrl = Me.txtBox1" use the next line of
code
Set myCtrl = Me.Controls(myTextName)

'manipulate the control

End Sub

Creating something in this manner will allow me to use the code
systematically. *If I have multiple controls, it becomes a bit
cumbersome to change the "Set myCtrl = Me.txtBox1" line of code to
"Me.txtBox2" or "Me.txtBox3", etc. for each of the _Exit events (or
any container event for that matter).

I'm still experimenting and looking around.

Best,

Matt- Hide quoted text -

- Show quoted text -

Well, here is a post from myself with a solution.

Dim thisCtrl As Control
Dim lngCodeLn As Long
Dim lngStartLn As Long
Dim lngStartCol As Long
Dim lngEndLn As Long
Dim lngEndCol As Long
Dim strCtrlText As String
Dim codeMod As VBIDE.CodeModule

Private Sub txtBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Application.VBE.ActiveCodePane.GetSelection lngStartLn, lngStartCol,
lngEndLn, lngEndCol
lngCodeLn = lngStartLn
Set codeMod = ActiveWorkbook.VBProject.VBComponents
(Me.Name).CodeModule
strCtrlText = GetEventControlName(codeMod, lngCodeLn)

Set thisCtrl = Me.Controls(strCtrlText)

'manipulate the control

End Sub

Function GetEventControlName(VBCodeMod As VBIDE.CodeModule, lngLn As
Long)
Dim strProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind '0 is Sub or Function
Dim lngUnderscore As Long
Dim strCtrlName As String

strProcName = VBCodeMod.ProcOfLine(lngLn, ProcKind)

'get the first "_" starting from the right and moving to the left
lngUnderscore = InStrRev(strProcName, "_", , vbTextCompare)

'get the text to the left of the "_"
strCtrlName = Left(strProcName, lngUnderscore - 1)

GetEventControlName = strCtrlName

End Function

Best,

Matt- Hide quoted text -

- Show quoted text -

Take note that this solution has not been completely tested, so it may
not be fully functional in its current form.

Matt