I have a form that I use to show or hide sheets in Excel.
It has 4 checkboxes with various search strings as a caption.
For instance,
Checkbox1.Caption = "DAB" and when that box is checked, the form shows all
the sheet names that contain "DAB".

I want to make this form more usable where I could transfer it in other
workbooks or have the user change the default search string. To do this I
need to be able to change the caption and have it "stick" once the dialog box
is closed.

The problem is that once I close the dialog box and re-open it, the caption
goes back to what it was before. How do I save the form design automatically
before the form is closed by the user?

Here's the code I have that changes the caption temporarily, but not
permanent:

Private Sub Image1_Click()
'This will allow user to change the label of the search check boxes
CheckBox1.Caption = Application.InputBox(prompt:="Checkbox 1",
Default:=CheckBox1.Caption)
End Sub


Thanks,
MikeZz

Default:=Sheets("Sourcedata").Range("A1")

Just one idea.

Jim

"MikeZz" wrote:

I have a form that I use to show or hide sheets in Excel.
It has 4 checkboxes with various search strings as a caption.
For instance,
Checkbox1.Caption = "DAB" and when that box is checked, the form shows all
the sheet names that contain "DAB".

I want to make this form more usable where I could transfer it in other
workbooks or have the user change the default search string. To do this I
need to be able to change the caption and have it "stick" once the dialog box
is closed.

The problem is that once I close the dialog box and re-open it, the caption
goes back to what it was before. How do I save the form design automatically
before the form is closed by the user?

Here's the code I have that changes the caption temporarily, but not
permanent:

Private Sub Image1_Click()
'This will allow user to change the label of the search check boxes
CheckBox1.Caption = Application.InputBox(prompt:="Checkbox 1",
Default:=CheckBox1.Caption)
End Sub


Thanks,
MikeZz

Here is sample code that permanently alters a userform:

Sub Changefont()
Dim fnt As Font
Dim ctrl As Object
Dim frm As Object
Set frm = ThisWorkbook.VBProject.VBComponents("Userform1")
For Each ctrl In frm.Designer.Controls
On Error Resume Next
ctrl.Font.Name = "Arial"
On Error GoTo 0
Next

End Sub

Adapt it to do what you want.

--
Regards,
Tom Ogilvy


"MikeZz" wrote:

I have a form that I use to show or hide sheets in Excel.
It has 4 checkboxes with various search strings as a caption.
For instance,
Checkbox1.Caption = "DAB" and when that box is checked, the form shows all
the sheet names that contain "DAB".

I want to make this form more usable where I could transfer it in other
workbooks or have the user change the default search string. To do this I
need to be able to change the caption and have it "stick" once the dialog box
is closed.

The problem is that once I close the dialog box and re-open it, the caption
goes back to what it was before. How do I save the form design automatically
before the form is closed by the user?

Here's the code I have that changes the caption temporarily, but not
permanent:

Private Sub Image1_Click()
'This will allow user to change the label of the search check boxes
CheckBox1.Caption = Application.InputBox(prompt:="Checkbox 1",
Default:=CheckBox1.Caption)
End Sub


Thanks,
MikeZz