get HelpContextID's or Tags of all userforms

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
R

RB Smissaert

Is there a way to get all the HelpContextID's or Tags of all the userforms
in a project?
I can get all the names of all the forms, but sofar no success to get the
HelpContextID's or Tags.

Sub test()

Dim oVBProj As VBProject
Dim oVBComp As VBComponent

Set oVBProj = ThisWorkbook.VBProject

On Error Resume Next

For Each oVBComp In oVBProj.VBComponents
If oVBComp.Type = 3 Then
MsgBox oVBComp.Name
End If
Next

End Sub

Thanks for any advice.


RBS
 
Hi Bart,

Try inserting the following after your "If oVBComp.Type = 3 Then"

For Each p In oVBComp.Properties
'Debug.Print p.Name
If p.Name = "Tag" Then
MsgBox p.Value, , oVBComp.Name
End If
If p.Name = "HelpContextID" Then
MsgBox p.Value, , oVBComp.Name
End If
Next

Of course if you could get same from the userform object though that means
loading it.

Regards,
Peter T
 
Hi Peter,

Thanks, will give that a try.
In fact I need to list all the HelpContextID's and Tags of all the possible
controls of all the Userforms
in the project.
This in connection with a big html help (WebHelp) file.
So I will need 2 For Each loops.

RBS
 
In fact I need to list all the HelpContextID's and Tags of all the
possible
controls of all the Userforms
Click to expand...

For each ctl In oVBComp.Designer.Controls
debug.? ctl.Tag, ctl.HelpContextID
Next

FWIW, you can also "permanently" write these properties subject to then
saving the project.

Regards,
Peter T
 
Peter,

Thanks, nice and simple. Will try that in a bit.

Are you saying that I can set these properties at runtime
and then save the project and retain it?
That would be a great help as I could then for example
do it all from a sheet range, rather then going into each control.

RBS
 
Peter,

How did you declare p?

RBS

Peter T said:
Hi Bart,

Try inserting the following after your "If oVBComp.Type = 3 Then"

For Each p In oVBComp.Properties
'Debug.Print p.Name
If p.Name = "Tag" Then
MsgBox p.Value, , oVBComp.Name
End If
If p.Name = "HelpContextID" Then
MsgBox p.Value, , oVBComp.Name
End If
Next

Of course if you could get same from the userform object though that means
loading it.

Regards,
Peter T
Click to expand...
 
I got this mostly worked out now except I need the type of control as well:

Sub tester()

Dim oVBProj As VBProject
Dim oVBComp As VBComponent
Dim ctl As MSForms.Control

Set oVBProj = ThisWorkbook.VBProject

On Error Resume Next

For Each oVBComp In oVBProj.VBComponents
If oVBComp.Type = 3 Then
For Each ctl In oVBComp.Designer.Controls
If ctl.Tag <> "" Or ctl.HelpContextID > 0 Then
MsgBox "Tag:" & vbTab & ctl.Tag & _
vbCrLf & _
"HelpID:" & vbTab & ctl.HelpContextID, , _
ctl.Name
End If
Next
End If
Next

End Sub

It doesn't look you can get the control type from oVBComp.Designer.Controls

RBS
 
Here is a little trick:

Sub abc()
Dim oVBComp As VBComponent
Dim p As Object
For Each oVBComp In ThisWorkbook.VBProject.VBComponents
If oVBComp.Type = 3 Then
For Each p In oVBComp.Properties
Debug.Print TypeName(p)
Exit Sub
Next
End If
Next
End Sub


Displays

Property
 
Hi Bart,

Not sure about "runtime" of the actual form you are re-designing. Not tried
but I don't see why not.

I have a complex form that, when I make design changes, I rebuild from
scratch from a cell range of properties and save the workbook with the newly
built form.

First I dump all details into cells of the old form, starting with the type
of control -
TypeName(.Controls(i)) ' see sControlType below

I make whatever changes in cells, eg add/delete controls, change properties,
tab orders etc. Then -

msFrmNme = name of an empty form that exists and about to rebuild

With ThisWorkbook.VBProject.VBComponents(msFrmNme).Designer
get a loop going to extract from cells (I put all details into a variant
array)

s = "Forms." & sControlType & ".1" '
..Controls.Add (s)
now add properties from other cells in the same row

There's a bit more to it, eg do tab-orders separately after sorting the rows
of control properties into tab order. Also adding to frames slightly
different, Images and some font properties need to be done separately.

The boring bit is writing code to cater for all control properties you are
likely to be concerned with!

Regards,
Peter T
 
TypeName(ctl)

--
Regards,
Tom Ogilvy


RB Smissaert said:
I got this mostly worked out now except I need the type of control as well:

Sub tester()

Dim oVBProj As VBProject
Dim oVBComp As VBComponent
Dim ctl As MSForms.Control

Set oVBProj = ThisWorkbook.VBProject

On Error Resume Next

For Each oVBComp In oVBProj.VBComponents
If oVBComp.Type = 3 Then
For Each ctl In oVBComp.Designer.Controls
If ctl.Tag <> "" Or ctl.HelpContextID > 0 Then
MsgBox "Tag:" & vbTab & ctl.Tag & _
vbCrLf & _
"HelpID:" & vbTab & ctl.HelpContextID, , _
ctl.Name
End If
Next
End If
Next

End Sub

It doesn't look you can get the control type from oVBComp.Designer.Controls

RBS
Click to expand...
 
OK, but what is the purpose of this Tom?

RBS

Tom Ogilvy said:
Here is a little trick:

Sub abc()
Dim oVBComp As VBComponent
Dim p As Object
For Each oVBComp In ThisWorkbook.VBProject.VBComponents
If oVBComp.Type = 3 Then
For Each p In oVBComp.Properties
Debug.Print TypeName(p)
Exit Sub
Next
End If
Next
End Sub


Displays

Property
Click to expand...
 
Strange response since you asked the question.

The purpose is to show you how to answer the question you asked.
How did you declare p?
Click to expand...

then the answer is as revealed by the code

Dim p as Property

If using late binding, then it would be

Dim p as Object

But that didn't appear to be the case.
 
OK, OK, I can see now.
I just had lost track of the questions and answers.
Got this all worked out now the way I need it:

Sub ShowHelpContextIDsAndTags()

Dim oVBProj As VBProject
Dim oVBComp As VBComponent
Dim ctl As MSForms.Control
Dim strFormName As String
Dim strFormTag As String
Dim lFormHelpID As Long
Dim oProp As Object
Dim i As Long

Application.ScreenUpdating = False

Cells.Clear

Cells(1) = "Control Type"
Cells(2) = "Form Name"
Cells(3) = "Form Help ID"
Cells(4) = "Form Tag"
Cells(5) = "Control Name"
Cells(6) = "Control Tag"
Cells(7) = "Control Help ID"

Range(Cells(1), Cells(7)).Font.Bold = True
MediumBottomBorder Range(Cells(1), Cells(7))

i = 1

Set oVBProj = ThisWorkbook.VBProject

On Error Resume Next

For Each oVBComp In oVBProj.VBComponents
If oVBComp.Type = 3 Then
strFormName = ""
strFormTag = ""
lFormHelpID = 0
For Each oProp In oVBComp.Properties
If oProp.Name = "Name" Then
strFormName = oProp.Value
Exit For
End If
Next
For Each oProp In oVBComp.Properties
If oProp.Name = "HelpContextID" Then
lFormHelpID = oProp.Value
Exit For
End If
Next
For Each oProp In oVBComp.Properties
If oProp.Name = "Tag" Then
strFormTag = oProp.Value
Exit For
End If
Next
For Each ctl In oVBComp.Designer.Controls
If TypeName(ctl) <> "Image" And _
TypeName(ctl) <> "ImageList" And _
TypeName(ctl) <> "CommonDialog" Then
If ctl.Tag <> "" Then
i = i + 1
Cells(i, 1) = TypeName(ctl)
Cells(i, 2) = strFormName
Cells(i, 3) = lFormHelpID
Cells(i, 4) = strFormTag
Cells(i, 5) = ctl.Name
Cells(i, 6) = ctl.Tag
Cells(i, 7) = ctl.HelpContextID
Else
If ctl.HelpContextID > 0 Then
i = i + 1
Cells(i, 1) = TypeName(ctl)
Cells(i, 2) = strFormName
Cells(i, 3) = lFormHelpID
Cells(i, 4) = strFormTag
Cells(i, 5) = ctl.Name
Cells(i, 6) = ctl.Tag
Cells(i, 7) = ctl.HelpContextID
End If
End If
End If
Next
End If
Next

With Range(Cells(1), Cells(i, 7))
.Columns.AutoFit
.HorizontalAlignment = xlLeft
.Name = "HelpContextIDsAndTags"
End With

Application.ScreenUpdating = True

End Sub

Sub MediumBottomBorder(rng As Range)
With rng
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With
End Sub


RBS
 
Hi again,

Not sure why I originally suggested looping through the properties but you
could also do this (though not tested with your code) -

With oVBComp
strFormName = .Properties("Name") ' defaults to .Value
' or simply
' strFormName = .Name
strFormTag = .Properties("Tag")
lFormHelpID = .Properties("HelpContextID")

End With

Regards,
Peter T
 
This works:
strFormName = oVBComp.Properties("Name")

But this:
strFormName = oVBComp.Name
doesn't.

For some reason I have to run the Sub twice to get the Form names in the
sheet. No idea why that is.
Otherwise all working nicely.

RBS
 
This works:
strFormName = oVBComp.Properties("Name")

But this:
strFormName = oVBComp.Name
doesn't.
Click to expand...

That's strange, it worked for me.

Also, is it not similar to the routine in your first post -

For Each oVBComp In oVBProj.VBComponents
If oVBComp.Type = 3 Then
MsgBox oVBComp.Name
End If
Next
For some reason I have to run the Sub twice to get the Form names in the
sheet. No idea why that is.
Click to expand...

This is also strange. If you put break in a strategic place, say after "If
oVBComp.Type = 3", what do you see in the locals window.

I haven't tried your code but I see you have On Error Resume Next. I can't
see an obvious reason for any errors at all, but try a proper error handler.
If it stops step through with F8 and figure out what went wrong (Locals ?).

Dim bDebug as boolean
bDebug = true
On Error goto errH
'code

ExitSub
errH:
if bDebug then
application.screenupdating = true
Stop
Resume
Else
Resume next
End if
End Sub

FWIW, in my very quick testing before posting I hadn't bothered setting a
ref to Extensibility. Late binding which meant I also declared both oVBProj
& oVBComp as Object (didn't intend admitting to that <g>).

Not related, but for curiosity have you tried writing properties with
Designer.

Regards,
Peter T
 
Hi Peter,
That's strange, it worked for me
Click to expand...

Did you try with the same code?

Yes, the error could be better and will have a look at that.
Not related, but for curiosity have you tried writing
properties with Designer.
Click to expand...

Not yet, but definitely will try that as it is going to save a lot of work.

RBS
 
Hi Bart,

I haven't tried the large routine you recently posted but I had tried the
example in your OP as well as variations of my own, and never had a problem
to return -

oVBComp.Name

BUT, I've just been playing around adding & deleting userforms manually &
programmatically and to my surprise that failed. Yet
oVBComp.Properties("Name") worked. Very odd, though I have not since been
able to recreate the error in a new project.

I don't think related but one thing that's often frustrated me -
if I rename a form, say "myForm", remove the form, add a new form, I can't
then rename it "myForm" (Path/File access error). At least not until after
saving the file.

Regards,
Peter T
 
Back
Top