| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
get HelpContextID's or Tags of all userforms
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
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 T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
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).Desi gner 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 "RB Smissaert" wrote in message ... 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 T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
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 "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
TypeName(ctl)
-- Regards, Tom Ogilvy "RB Smissaert" wrote in message ... 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 "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
Peter,
How did you declare p? RBS "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
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" wrote in message ... Peter, How did you declare p? RBS "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
OK, but what is the purpose of this Tom?
RBS "Tom Ogilvy" wrote in message ... 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" wrote in message ... Peter, How did you declare p? RBS "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
get HelpContextID's or Tags of all userforms
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" wrote in message ... OK, but what is the purpose of this Tom? RBS "Tom Ogilvy" wrote in message ... 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" wrote in message ... Peter, How did you declare p? RBS "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| name tags | Excel Discussion (Misc queries) | |||
| SMART TAGS | Excel Discussion (Misc queries) | |||
| Smart Tags | Excel Discussion (Misc queries) | |||
| Smart Tags | Excel Worksheet Functions | |||
| Smart tags | Excel Worksheet Functions | |||
Hybrid Mode
