checkbox logic

Alex Simmons

New Member
Joined
Dec 3, 2012
Messages
17
I'm trying to sort out my checkbox logic - but what I have isn't doing what I expect.

what I'm attempting to do is have:


  1. a userform with checkboxes (say 2 checkboxes in this example)
  2. the checkboxes to auto-populate with True/False based on the pre-existing values in cells A1, A2 of Sheet1
  3. the user then can change the selection in the checkboxes on the userform, which then updates the values in cells A1, A2

this way, when they reopen the same userform later, the previously set values are shown in the checkboxes.

My final intent is to provide the user with a way to select which named ranges of the workbook to print-preview but recognise that they will most often just want to use the setting they previously chose, and not require them to reselect the pages to preview each time.

Sheet attached (ah, no it's not - I thought I could attach my sheet?).

This is the code I have for the userform and checkboxes.

What am I doing wrong?
thanks!

Code:
Private Sub UserForm_Activate()
'   when opening the userform set the checkboxes on
'   userform to show the values stored in cells A1 & A2

    CheckBox1.Value = Sheets("Sheet1").Range("A1").Value
    CheckBox2.Value = Sheets("Sheet1").Range("A2").Value
    
'   however for some reason this is toggling the cell
'   values instead of displaying what was previously there

'   I just want it to show what's already in the cells
'   and not change their value

End Sub


Private Sub CheckBox1_Change()
'   when user changes checkbox1 value on userform
'   then toggle the stored (true/false) value in cell A1

    If Sheets("Sheet1").Range("A1").Value = True _
    Then Sheets("Sheet1").Range("A1").Value = False _
    Else Sheets("Sheet1").Range("A1").Value = True

'   what this is doing is toggling the value in cell A1, but the
'   checkbox displays a value opposite to that stored in the cell.

'   A TRUE is cell A1 shows on the checkbox as not checked, and
'   a FALSE in cell A1 shows on the checkbox as checked.

End Sub


Private Sub CheckBox2_Change()
'   same as for checkbox1 and cell A1

    If Sheets("Sheet1").Range("A2").Value = True _
    Then Sheets("Sheet1").Range("A2").Value = False _
    Else Sheets("Sheet1").Range("A2").Value = True

End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Shouldn't you be checking the value of the checkbox rather than a cell?
 
Upvote 0
Shouldn't you be checking the value of the checkbox rather than a cell?
Apparently. :)

Thanks for the pointer, that did the trick, along with adjusting the If-Then-Else statements to set cell value accordingly.

Here is the code that works:


Code:
'   Private subs to enable checkboxes on a userform
'   to display value stored in cells A1 & A2 on Sheet1
'   and then to update same cells


Private Sub UserForm_Activate()
'   when opening the userform set the checkboxes on
'   userform to show the values stored in cells A1 & A2


    CheckBox1.Value = Sheets("Sheet1").Range("A1").Value
    CheckBox2.Value = Sheets("Sheet1").Range("A2").Value
    
End Sub


Private Sub CheckBox1_Change()
'   when user changes checkbox1 value on userform
'   then assign that value to cell A1 on Sheet1


    If CheckBox1.Value = True _
    Then Sheets("Sheet1").Range("A1").Value = True _
    Else Sheets("Sheet1").Range("A1").Value = False




End Sub


Private Sub CheckBox2_Change()
'   when user changes checkbox2 value on userform
'   then assign that value to cell A2 on Sheet2
    
    If CheckBox2.Value = True _
    Then Sheets("Sheet1").Range("A2").Value = True _
    Else Sheets("Sheet1").Range("A2").Value = False


End Sub

I have so much I want to make my current project do, and so little knowledge of VBA to make it happen. I seem to be able to make spreadsheets do all sorts of funky things but the simplest things on VBA elude me at times.

I have a couple of books heading my way to help me learn.
 
Upvote 0
You can always ask for help here.:)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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
Back
Top