Checkbox help

[LSpliff]

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!

 

[Sharkie21]

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]

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]

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]

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]

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]

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]

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]

Change "If CheckBox1.Value = True Then" to "If CheckBox3.Value = True Then"

Smitty

 

[LSpliff]

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.