Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all label controltiptexts in userform
Hi guys,
This is driving me insane!!! I'm trying to have the text of all of the controltiptext properties on a userform to be the same as the caption property of the label (there's loads of labels). It seemed like it should have been easy, but after much trial and error I've managed to come up with the following (which I don't quite follow) and feels so close, but I'm stumped as to get the caption in the Sub Change_Labels_UserForm() Dim oVBProj, oVBComp As Object Dim ctl As 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 Left(ctl.Name, 5) = "Label" and ctl.ControlTipText = "" Then ctl.ControlTipText = 'caption name Next End If Next End Sub Any ideas? Cheers, JF |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all label controltiptexts in userform
Hi JF,
You pretty much had it correct already, and the slight modification of your code shown below works for me. Note that you're only changing the ControlTipText if there isn't an entry already, so this may be confusing the results. Sub Change_Labels_UserForm() Dim oVBComp As Object Dim ctl As Control On Error Resume Next For Each oVBComp In ThisWorkbook.VBProject.VBComponents If oVBComp.Type = 3 Then For Each ctl In oVBComp.Designer.Controls If TypeName(ctl) = "Label" And ctl.ControlTipText = "" Then ctl.ControlTipText = ctl.Caption End If Next ctl End If Next oVBComp End Sub -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm wrote in message ... Hi guys, This is driving me insane!!! I'm trying to have the text of all of the controltiptext properties on a userform to be the same as the caption property of the label (there's loads of labels). It seemed like it should have been easy, but after much trial and error I've managed to come up with the following (which I don't quite follow) and feels so close, but I'm stumped as to get the caption in the Sub Change_Labels_UserForm() Dim oVBProj, oVBComp As Object Dim ctl As 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 Left(ctl.Name, 5) = "Label" and ctl.ControlTipText = "" Then ctl.ControlTipText = 'caption name Next End If Next End Sub Any ideas? Cheers, JF |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all label controltiptexts in userform
I'm not 100% sure what you are asking here. First, we are talking about the
controls on a UserForm, right? Second, are you asking to change the ControlTipText for Labels only? If so, is the condition that Labels **without** a ControlTipText assigned to it should show its own Caption (and those that do have a ControlTipText assign to it should continue to show that)? If so, give this code a try... Sub Change_Labels_UserForm() Dim ctl As Control For Each ctl In UserForm1.Controls If TypeOf ctl Is MSForms.Label Then If ctl.ControlTipText = "" Then ctl.ControlTipText = ctl.Caption End If Next End Sub Rick wrote in message ... Hi guys, This is driving me insane!!! I'm trying to have the text of all of the controltiptext properties on a userform to be the same as the caption property of the label (there's loads of labels). It seemed like it should have been easy, but after much trial and error I've managed to come up with the following (which I don't quite follow) and feels so close, but I'm stumped as to get the caption in the Sub Change_Labels_UserForm() Dim oVBProj, oVBComp As Object Dim ctl As 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 Left(ctl.Name, 5) = "Label" and ctl.ControlTipText = "" Then ctl.ControlTipText = 'caption name Next End If Next End Sub Any ideas? Cheers, JF |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all label controltiptexts in userform
Rick, the OP is asking about programmatically "designing" a Userform
(actually all the userforms), such that when the code terminates the changes to control properties persist. Unfortunately there's no VB6 equivalent method. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... I'm not 100% sure what you are asking here. First, we are talking about the controls on a UserForm, right? Second, are you asking to change the ControlTipText for Labels only? If so, is the condition that Labels **without** a ControlTipText assigned to it should show its own Caption (and those that do have a ControlTipText assign to it should continue to show that)? If so, give this code a try... Sub Change_Labels_UserForm() Dim ctl As Control For Each ctl In UserForm1.Controls If TypeOf ctl Is MSForms.Label Then If ctl.ControlTipText = "" Then ctl.ControlTipText = ctl.Caption End If Next End Sub Rick wrote in message ... Hi guys, This is driving me insane!!! I'm trying to have the text of all of the controltiptext properties on a userform to be the same as the caption property of the label (there's loads of labels). It seemed like it should have been easy, but after much trial and error I've managed to come up with the following (which I don't quite follow) and feels so close, but I'm stumped as to get the caption in the Sub Change_Labels_UserForm() Dim oVBProj, oVBComp As Object Dim ctl As 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 Left(ctl.Name, 5) = "Label" and ctl.ControlTipText = "" Then ctl.ControlTipText = 'caption name Next End If Next End Sub Any ideas? Cheers, JF |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all label controltiptexts in userform
Thanks for clearing that up for me. As for not being able to do it in VB6...
while I'm still not 100% sure of what the OP is after, I would think preserving changes should theoretically be able to be done by writing out the changes to a file or the registry in some coded fashion and then reading them back in when the UserForm is initialized. Rick "Peter T" <peter_t@discussions wrote in message ... Rick, the OP is asking about programmatically "designing" a Userform (actually all the userforms), such that when the code terminates the changes to control properties persist. Unfortunately there's no VB6 equivalent method. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... I'm not 100% sure what you are asking here. First, we are talking about the controls on a UserForm, right? Second, are you asking to change the ControlTipText for Labels only? If so, is the condition that Labels **without** a ControlTipText assigned to it should show its own Caption (and those that do have a ControlTipText assign to it should continue to show that)? If so, give this code a try... Sub Change_Labels_UserForm() Dim ctl As Control For Each ctl In UserForm1.Controls If TypeOf ctl Is MSForms.Label Then If ctl.ControlTipText = "" Then ctl.ControlTipText = ctl.Caption End If Next End Sub Rick wrote in message ... Hi guys, This is driving me insane!!! I'm trying to have the text of all of the controltiptext properties on a userform to be the same as the caption property of the label (there's loads of labels). It seemed like it should have been easy, but after much trial and error I've managed to come up with the following (which I don't quite follow) and feels so close, but I'm stumped as to get the caption in the Sub Change_Labels_UserForm() Dim oVBProj, oVBComp As Object Dim ctl As 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 Left(ctl.Name, 5) = "Label" and ctl.ControlTipText = "" Then ctl.ControlTipText = 'caption name Next End If Next End Sub Any ideas? Cheers, JF |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all label controltiptexts in userform
H i Rick,
Thanks for clearing that up for me. I'm not sure I have ! As for not being able to do it in VB6... while I'm still not 100% sure of what the OP is after, The OP is looking to change his Labels' ControlTipText to read same as the name of the caption, but only if the ControlTipText property is empty. He wants to do this is a way that when the code terminates, the updated text remains as the ControlTipText property. If he has a lot of labels it's tedious to do them manually at design. I would think preserving changes should theoretically be able to be done by writing out the changes to a file or the registry in some coded fashion and then reading them back in when the UserForm is initialized. I take it you mean store property data somewhere, registry, text-file, or cells on a hidden sheet, then update the control properties in the Initialize event. That of course is possible; indeed for the OP's particular purpose I guess similar could be done in each form's initialize event by reading label captions pretty much as already doing. Perhaps there's a reason the OP wants the properties set at design. As for not being able to do it in VB6 With the VBA form's "Designer" you can create an entire form with controls from scratch. I have found this to be particularly where large numbers of controls are involved. Their names, types, properties etc can be designed literally in table form on a spreadsheet. Run some code that reads the cell data and convert into a new saveable form. AFAIK, the only way in VB6 to do something similar is to write the *.frm file as text. I once made a start but gave up! Regards, Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change all label controltiptexts in userform
Thanks Rob,
I swear I tried that! Maybe a case of looking at something for too long to be able to see the obvious? Cheers, JF On 25 Jul, 17:26, "Rob Bovey" wrote: Hi JF, * * You pretty much had it correct already, and the slight modification of your code shown below works for me. Note that you're only changing the ControlTipText if there isn't an entry already, so this may be confusing the results. Sub Change_Labels_UserForm() * * Dim oVBComp As Object * * Dim ctl As Control * * On Error Resume Next * * For Each oVBComp In ThisWorkbook.VBProject.VBComponents * * * * If oVBComp.Type = 3 Then * * * * * * For Each ctl In oVBComp.Designer.Controls * * * * * * * * If TypeName(ctl) = "Label" And ctl.ControlTipText = "" Then * * * * * * * * * * ctl.ControlTipText = ctl.Caption * * * * * * * * End If * * * * * * Next ctl * * * * End If * * Next oVBComp End Sub -- Rob Bovey, Excel MVP Application Professionalshttp://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm wrote in message ... Hi guys, This is driving me insane!!! I'm trying to have the text of all of the controltiptext properties on a userform to be the same as the caption property of the label (there's loads of labels). It seemed like it should have been easy, but after much trial and error I've managed to come up with the following (which I don't quite follow) and feels so close, but I'm stumped as to get the caption in the Sub Change_Labels_UserForm() Dim oVBProj, oVBComp As Object Dim ctl As 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 Left(ctl.Name, 5) = "Label" and ctl.ControlTipText = "" Then ctl.ControlTipText = 'caption name * *Next *End If *Next End Sub Any ideas? Cheers, JF- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform Label | Excel Discussion (Misc queries) | |||
userform label creation | Excel Programming | |||
Flashing UserForm Label | Excel Programming | |||
Change label caption on userForm while code runs | Excel Programming | |||
Userform Label question | Excel Programming |