Can anyone please tell me how to put a checkbox in an Excel Spead Sheet and when it is checked to add a value to a field?
Thanks in advance.
Can anyone please tell me how to put a checkbox in an Excel Spead Sheet and when it is checked to add a value to a field?
Thanks in advance.
Depending what you require, but something like:
Private Sub CheckBox1_Click()
Const ExtraValue As Long = 25
If CheckBox1.Value = True Then
Range("A1").Value = Range("A1").Value + ExtraValue
Else
'Decide what you do here
End If
End Sub
NickHK
"emel24" <[email protected]> wrote in
message news:[email protected]...
>
> Can anyone please tell me how to put a checkbox in an Excel Spead Sheet
> and when it is checked to add a value to a field?
>
> Thanks in advance.
>
>
> --
> emel24
> ------------------------------------------------------------------------
> emel24's Profile:
http://www.excelforum.com/member.php...o&userid=20004
> View this thread: http://www.excelforum.com/showthread...hreadid=556792
>
Can you please advise where to enter this?
Is this a Macro?
Thanks in advance.
After you have added your combo box to the worksheet, view its code and this
is one of its events.
Private Sub CheckBox1_Click()
I assume you are using the check box from the Controls toolbox, not from
Forms.
NickHK
"emel24" <[email protected]> wrote in
message news:[email protected]...
>
> Can you please advise where to enter this?
> Is this a Macro?
>
> Thanks in advance.
>
>
> --
> emel24
> ------------------------------------------------------------------------
> emel24's Profile:
http://www.excelforum.com/member.php...o&userid=20004
> View this thread: http://www.excelforum.com/showthread...hreadid=556792
>
Yes, I wasn't using the Controls toolbox to create the checkbox. Your formular works now.
However I am trying to do the following:
When checkbox1 is ticked I want to show 5% of the sum of a column in another field. And when checkbox2 is ticked I want to show 10% of the sum in another field.
For example: A7 = 500
When checkbox1 is ticked add 5%
Show value in A8 (being 25) if not ticked don't show a value
When checkbox 2 is ticket add 10%
Show value in A9 (being 50) if not ticked don't show a value
Thanks for your help! :-)
I have just noticed that when I tick the box it does the 5% however if I untick it again it doesn't set it back to 0.
How can I do it, that whenever it is ticked or unticked to change the value?
Just edit the previous code slightly:
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Range("A8").Value = Range("A7").Value * 1.05
Else
Range("A8").Value = 0 'Or "" if you want nothing to show
End If
End Sub
'This is effectively the same as the code above, FALSE=0, TRUE= -1
Private Sub CheckBox2_Click()
Range("A9").Value = Range("A7").Value * 1.1 * (CheckBox2.Value = True) * -1
End Sub
NickHK
"emel24" <[email protected]> wrote in
message news:[email protected]...
>
> Yes, I wasn't using the Controls toolbox to create the checkbox. Your
> formular works now.
>
> However I am trying to do the following:
> When checkbox1 is ticked I want to show 5% of the sum of a column in
> another field. And when checkbox2 is ticked I want to show 10% of the
> sum in another field.
>
> For example: A7 = 500
> When checkbox1 is ticked add 5%
> Show value in A8 (being 25) if not ticked don't show a value
> When checkbox 2 is ticket add 10%
> Show value in A9 (being 50) if not ticked don't show a value
>
> Thanks for your help! :-)
>
>
> --
> emel24
> ------------------------------------------------------------------------
> emel24's Profile:
http://www.excelforum.com/member.php...o&userid=20004
> View this thread: http://www.excelforum.com/showthread...hreadid=556792
>
Thank you very much for all your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks