Re: get HelpContextID's or Tags of all userforms

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

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
news:#YRq8T20FHA.664@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <peter_t@discussions> wrote in message
> news:uwFuCQy0FHA.3780@xxxxxxxxxxxxxxxxxxxxxxx
> >> 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
> >> ***. 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" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
> > news:eKndyQx0FHA.4032@xxxxxxxxxxxxxxxxxxxxxxx
> >> 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
> >> ***. No idea why that is.
> >> Otherwise all working nicely.
> >>
> >> RBS
> >>
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:Ov2WuJq0FHA.2008@xxxxxxxxxxxxxxxxxxxxxxx
> >> > 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" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
> >> > news:OK#lp1p0FHA.404@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> 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
> >> >>
> >> >>
> >> >> "Tom Ogilvy" <twogilvy@xxxxxxx> wrote in message
> >> >> news:uNbaAbp0FHA.3956@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> > 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.
> >> >> >
> >> >> > --
> >> >> > Regards,
> >> >> > Tom Ogilvy
> >> >> >
> >> >> >
> >> >> > "RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
> >> >> > news:OkMDLQp0FHA.728@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> >> OK, but what is the purpose of this Tom?
> >> >> >>
> >> >> >> RBS
> >> >> >>
> >> >> >> "Tom Ogilvy" <twogilvy@xxxxxxx> wrote in message
> >> >> >> news:eLD1PVo0FHA.3780@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> >> > 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
> >> >> >> >
> >> >> >> > --
> >> >> >> > Regards,
> >> >> >> > Tom Ogilvy
> >> >> >> >
> >> >> >> >
> >> >> >> > "RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in
message
> >> >> >> > news:OvlJsBo0FHA.3020@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> >> >> Peter,
> >> >> >> >>
> >> >> >> >> How did you declare p?
> >> >> >> >>
> >> >> >> >> RBS
> >> >> >> >>
> >> >> >> >> "Peter T" <peter_t@discussions> wrote in message
> >> >> >> >> news:OEljXjm0FHA.3756@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> >> >> > 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" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in
> > message
> >> >> >> >> > news:#W$7zxl0FHA.2752@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> >> >> >> 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
> >> >> >> >> >>
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >
> >> >
> >>
> >
> >
>


.