Find / Replace text in Excel Userforms

[senators40]

Hi,

I have several forms with text of different years. Each year I have to upgrade the information to match the following year (ie if the text stated 2006 Actuals I have to change it to 2007 Actuals)

Within the code portion I can use the find / replace to change all the code, however if I have 2006 Actuals in Label1 on the specific userform there does not seem to be a way to find / replace that information.

I tried to export the form, manually edit the .frx file and then import the form and I got an error.

Could you let me know if there is a way to edit the onscreen text on a userform using find / replace

Thanks,

Jeff

 

[SkipVought]

Hi,

Maybe the YEAR this label ought to be VARIABLE. Then once a year you change the variable assignment. Or make the variable assignment base on the DATE.

Then, it would not be a maintenance headache each year.

Skip,

_[red][/red]

 

[firefytr]

If you're talking labels, put some code in your userform_initialize event, something to the effect of ...

label1.caption = format(date, "yyyy")

HTH

-----------
Regards,
Zack Barresse

 

[CautionMP]

senators40,
I agree with Skip, re-code your label captions to be "smart", in the mean time the following will update your labels (it works in Excel 2000 with a reference to Microsoft Visual Basic for Extensibility x.x added).

Sub UpdateFormLabels()
[b][green]'In order to run this you will need to add a project reference to
'Microsoft Visual Basic for Extensibility x.x[/green][/b]
Dim prjCurrent
Dim comCurrent As VBComponent
Dim ctlCurrent As MSForms.Control

Set prjCurrent = ThisWorkbook.VBProject

For Each comCurrent In prjCurrent.VBComponents
  If comCurrent.Type = vbext_ct_MSForm Then
    For Each ctlCurrent In comCurrent.Designer.Controls
      [b][green]'Here is where 2006 gets changed to 2008[/green][/b]
      ctlCurrent.Caption = Replace(ctlCurrent.Caption, "2006", "2008", , , vbTextCompare)
    Next ctlCurrent
  End If
Next comCurrent
Set ctlCurrent = Nothing
Set comCurrent = Nothing
Set prjCurrent = Nothing
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[senators40]

Thank you for the response it worked great on the forms with only labels (or command buttons)

Is there a way to change a textbox name as well.

Unfortunately when a form has both labels (command buttons also change) and textboxes the textboxes give the error message (whether the year 2006 is in the textbox name or not.

(runtime error 438 Object does not support this property or method))

The name could be Salaries_2006 for the textbox name

Thanks in advance,

Jeff

 

[PHV]

Who matter the name of a TextBox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

I think I'm going to side with [navy]PHV[/navy] on this one. Why would you want to change the name of a text box? If it ain't broke...
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[fumei]

Ditto. Labels display information, they are read by the user. The name of the textbox is never seen by the user. Why is there any need to change it?

Gerry
My paintings and sculpture