Form and Code Assistance

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
I have created a form that allows a user to select multiple classes via a check box. Each Check box has a corresponding worksheet. What I am needing is code that will unhide the worksheet when the Checkbox is selected. Can anyone help. thx -asa
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,486
Office Version
  1. 365
Platform
  1. Windows
If your check box is named "CheckBox1", and if the sheet you are unhiding is "Sheet1", place the following code in the code section on the form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Sheets("Sheet1").Visible = True
End If
End Sub

Repeat for other check boxes. You could also add an "Else" statement setting the .Visible property to False if you want to rehide the sheet if they uncheck the box.
 

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
On 2002-10-24 14:12, jmiskey wrote:
If your check box is named "CheckBox1", and if the sheet you are unhiding is "Sheet1", place the following code in the code section on the form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Sheets("Sheet1").Visible = True
End If
End Sub

Repeat for other check boxes. You could also add an "Else" statement setting the .Visible property to False if you want to rehide the sheet if they uncheck the box.
That works great, thank you very much. BTW I went with the IF THEN ELSE statement. One more questions now. On my form, to unload the form I have Unload Me, that clears all the Check Boxes. Is there a way to keep them checked when the form is unloaded?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,486
Office Version
  1. 365
Platform
  1. Windows
How about adding another page to the workbook. Then add code that will update cells on the worksheet to indicate which boxes are checked. For example, after the unhide line in our code, update cell A1 on the new worksheet with any value (i.e. "Class1"). Assign each check box to a different cell.

Then, add code to the beginning that looks at the values and updates the form. For example, if the value in cell A1="Class1",
then CheckBox1.Value=True. That should check the box.

Hope this makes sense. If not, let me know and I will be more specific.
 

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030

ADVERTISEMENT

On 2002-10-24 15:07, Aaron wrote:
On 2002-10-24 14:12, jmiskey wrote:
If your check box is named "CheckBox1", and if the sheet you are unhiding is "Sheet1", place the following code in the code section on the form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Sheets("Sheet1").Visible = True
End If
End Sub

Repeat for other check boxes. You could also add an "Else" statement setting the .Visible property to False if you want to rehide the sheet if they uncheck the box.
That works great, thank you very much. BTW I went with the IF THEN ELSE statement. One more questions now. On my form, to unload the form I have Unload Me, that clears all the Check Boxes. Is there a way to keep them checked when the form is unloaded?

Are you wanting to keep the check box's checked all the time? if so just select all the checkboxes and goto the properties and set the value to true, if your just wanting to hold what the user has selected load another form then come back to the original form then you just have to hide the userform not unload it!

Brett
 

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
It sort of makes sense, but with that idea you have given me insight.

Is there a way to name the Check Box whatever you name the Worksheet? Essentially if the worksheet does not meet the users criteria, they should be able to change the name of it, and that would change the Check box name? Did that make sense at all?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,486
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you just care about holding the value while the file is open, use Brett's suggestion of hiding the form instead of unloading it. It is much easier.

If you want to save the selections from the previous time the file was open, you could store information like I mentioned in the previous e-mail (I haven't actually attempted to do this yet, so I am unaware if there are any "tricks" involved).

I am afraid I do not understand what you were asking is the last reply, and if it was in reference to my reply or Brett's.
 

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
On 2002-10-24 16:36, jmiskey wrote:
If you just care about holding the value while the file is open, use Brett's suggestion of hiding the form instead of unloading it. It is much easier.

If you want to save the selections from the previous time the file was open, you could store information like I mentioned in the previous e-mail (I haven't actually attempted to do this yet, so I am unaware if there are any "tricks" involved).

I am afraid I do not understand what you were asking is the last reply, and if it was in reference to my reply or Brett's.

I was referring to your message. However I think Brett's suggestion is what I will use. Sorry my first question wasn't very clear.

I was wondering, if there was a way you could name your checkbox from your worksheet name. If Sheet1 was changed to Total, could changing my worksheet name to Total change the CheckBox name to Total?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,486
Office Version
  1. 365
Platform
  1. Windows
In regards to your question, I know how to do some of that. You could add a command button next to each check box on the form that says "Change Caption". When this command button is clicked, have it use an InputBox so the user can enter the new caption. Then use the CheckBox1.Caption property to reset the caption.

However, if you do this, you may want to use generic names for the sheets, like Sheet1, Sheet2, etc and not change them. Here is why: if you change the names of the sheets, the macros will not run properly unless you manually edit the macro to change all the sheet references (I don't know if it is possible to run a macro to update a macro).

There may be a different way to do what you want, or there may be a way to write a macro to update another macro. If this is something you are interested in pursuing, I would recommend posting another question to the board so that everyone will look at it.
 

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
Thx jmiskey I appreciate all of your help. I will probably search on that topic, and if I don't find anything I will post that question. Once again thx for your help.
 

Forum statistics

Threads
1,144,213
Messages
5,723,055
Members
422,476
Latest member
beck85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top