get HelpContextID's or Tags of all userforms

[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

 

[Peter T]

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

 

[RB Smissaert]

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

 

[Peter T]

In fact I need to list all the HelpContextID's and Tags of all the
possible

controls of all the Userforms

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

 

[RB Smissaert]

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

 

[RB Smissaert]

Peter,

How did you declare p?

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

 

[RB Smissaert]

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

 

[Tom Ogilvy]

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

 

[Peter T]

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

 

[Tom Ogilvy]

TypeName(ctl)

--
Regards,
Tom Ogilvy

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

 

[RB Smissaert]

Thanks, that is the one I needed.

RBS

TypeName(ctl)

 

[RB Smissaert]

OK, but what is the purpose of this Tom?

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

 

[Tom Ogilvy]

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?

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.

 

[RB Smissaert]

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

 

[Peter T]

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

 

[RB Smissaert]

Peter,

OK, thanks, that simplifies it a bit.
Will put that in.

RBS

 

[RB Smissaert]

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

 

[Peter T]

This works:

strFormName = oVBComp.Properties("Name")

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

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.

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

 

[RB Smissaert]

Hi Peter,

That's strange, it worked for me

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.

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

RBS

 

[Peter T]

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