Checkbox help

LSpliff

New Member
Joined
Dec 19, 2005
Messages
8
Hi all... I needed to do some stuff for my job but I'm VB inept. What i need is a check box that, when unchecked, it renders lets say A35 to J46 inactive and greyed out where they cant enter any fields. Then when they check it, it opens up the fields. To make matters worse, I hardly use VB and I'll just say I know how to open the editor lol I only have 2 years of C++ under me =oP

If anyone wants to take the time to help me it would really be appreciated!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Sharkie21

Active Member
Joined
Nov 2, 2005
Messages
319
Alright.. First you need to add the checkbox which can be found in the control toolbox under view toolbar.

After you add a checkbox you need to edit the click event.

so double click on the checkbox and it should create the stub for a click event.

Next you want to check the value of the checkbox to see if it was checked or unchecked.

so you want

something like
Public Sub checkbox1_click()

If checkbox1.value = 1 then
'unlock
else
'lock
range("A35:J46").select
selection.interior.colorindex = 15 'maybe
'not sure how you lock the cells but perhaps someone can fill this in or I can look it up.

End If


End Sub
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

How's this:
<font face=tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox1_Click()
    <SPAN style="color:#00007F">If</SPAN> CheckBox1.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        ActiveSheet.Unprotect
            Range("A35:J46").Locked = <SPAN style="color:#00007F">False</SPAN>
        ActiveSheet.Protect
    <SPAN style="color:#00007F">Else</SPAN>
        ActiveSheet.Unprotect
            Range("A35:J46").Locked = <SPAN style="color:#00007F">True</SPAN>
        ActiveSheet.Protect
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty
 

LSpliff

New Member
Joined
Dec 19, 2005
Messages
8
ok.. got the click box in and its locked to a cell now... I put in penny's code and it didnt do anything when i clicked and unclicked. Gotta mess with it again... but i cant right click and edit now. I unprotected the sheet too =o/
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Where did you put the code?

To get into Edit mode for an ActiveX control, goto View-->Toolbars-->Control Toolbox. Edit mode is the Speed Square/Pencil icon in the upper right-hand corner. Once you're in Edit mode, double-clicking the Check Box will bring up its Click event. That's where the code goes.

Smitty
 

LSpliff

New Member
Joined
Dec 19, 2005
Messages
8
Ok.. straightened my BS out... but yeah I put it in that box and saved it. It doesnt want to protect it though. I can still type in the field that it should be protecting. I have it for checkbox3 right now that i want this in. How would I grey out the cells when they are protected too?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Did you change "Private Sub CheckBox1_Click()" to "Private Sub CheckBox3_Click()"?

As for setting the cell color, record a macro applying the color that you want & add the code to what you have already. In the unprotect portion, set the ColorIndex to 0.

Smitty
 

LSpliff

New Member
Joined
Dec 19, 2005
Messages
8
This is what I have:

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
ActiveSheet.Unprotect
Range("A36:J58").Locked = False
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
Range("A36:J58").Locked = True
ActiveSheet.Protect
End If
End Sub
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Change "If CheckBox1.Value = True Then" to "If CheckBox3.Value = True Then"

Smitty
 

LSpliff

New Member
Joined
Dec 19, 2005
Messages
8
Yeah sorry I fixed that before but didnt post it. Now... lol my prob is that it locks the whole sheet even though i have that range in.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,037
Messages
5,569,791
Members
412,292
Latest member
The Bear named Joe
Top