Checkboxes and macros

I'm new with working with forms and macros and I'm a little lost. This is
what I have...

A B C
3
2
5 0 0

This is what I need. I need to place 3 checkboxes each in B2, B3 and B4,
equally Yes,No and N/A. If yes is clicked, then B2 and C2 will populate with
value "3". If no is clicked, then B2 and C2 will populate with value "0". If
N/A is clicked then, then B2 and C2 will populate with "-".

Is this possible? And if so, is there a macro that I can place to do all of
this? How would I do that macro?

I'm sorry I'm so dense.....LOL!!

Thank you in advance!

Rightclick on the icon bar. Select Forms
Then Insert 3 Checkboxes. Rightclick on one of the boxes and select cell link. Select B5. Repeat with the other Checkboxes, and select links to B6, B7.

In B2 insert this formula: =CHOOSE(1+B5*1+B6*2+B7*3,"",3,0,"-")
If there is a risk that Yes and No is selected, add ,"","",""

Ola Sandström


See picture: http://www.excelforum.com/attachment...tid=3487&stc=1

Thank you so much. I'm trying to insert the formula in B2, but I'm having
trouble. How do I do that if the checkboxes are in the cell?

"olasa" wrote:

>
> Rightclick on the icon bar. Select Forms
> Then Insert 3 Checkboxes. Rightclick on one of the boxes and select
> cell link. Select B5. Repeat with the other Checkboxes, and select
> links to B6, B7.
>
> In B2 insert this formula: =CHOOSE(1+B5*1+B6*2+B7*3,"",3,0,"-")
> If there is a risk that Yes and No is selected, add ,"","",""
>
> Ola Sandström
>
>
> See picture:
> http://www.excelforum.com/attachment...tid=3487&stc=1
>
>
> +-------------------------------------------------------------------+
> |Filename: Clipboard01.jpg |
> |Download: http://www.excelforum.com/attachment.php?postid=3487 |
> +-------------------------------------------------------------------+
>
> --
> olasa
> ------------------------------------------------------------------------
> olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
> View this thread: http://www.excelforum.com/showthread...hreadid=378652
>
>

You might want to reconsider and use optionbuttons. Then when you choose one of
them, the other two are not chosen.

I put a GroupBox from the Forms toolbar around B2:B4
Then I added 3 optionbuttons to that GroupBox (yes, no, na)
Then I rightclicked on an optionbutton
selected format control|On the control tab, I gave it a nice cell link.
(I used A3 in my test.)

Then I used this formula to show the text.
=IF(A3="","",CHOOSE(A3,"Yes","N/A","-"))

Depending on how you label the yes/no/na optionbuttons, you may have to play
with that order.

Then I hid column A -- just so it wouldn't be noticeable. But you could use any
column you want and hide that column later.

danielle wrote:
>
> I'm new with working with forms and macros and I'm a little lost. This is
> what I have...
>
> A B C
> 3
> 2
> 5 0 0
>
> This is what I need. I need to place 3 checkboxes each in B2, B3 and B4,
> equally Yes,No and N/A. If yes is clicked, then B2 and C2 will populate with
> value "3". If no is clicked, then B2 and C2 will populate with value "0". If
> N/A is clicked then, then B2 and C2 will populate with "-".
>
> Is this possible? And if so, is there a macro that I can place to do all of
> this? How would I do that macro?
>
> I'm sorry I'm so dense.....LOL!!
>
> Thank you in advance!

--

Dave Peterson

How do I get my values to show up in in my C column? And is there a way to
have the checkboxes/option buttons to hide or disappear once they're checked?

"Dave Peterson" wrote:

> You might want to reconsider and use optionbuttons. Then when you choose one of
> them, the other two are not chosen.
>
> I put a GroupBox from the Forms toolbar around B2:B4
> Then I added 3 optionbuttons to that GroupBox (yes, no, na)
> Then I rightclicked on an optionbutton
> selected format control|On the control tab, I gave it a nice cell link.
> (I used A3 in my test.)
>
> Then I used this formula to show the text.
> =IF(A3="","",CHOOSE(A3,"Yes","N/A","-"))
>
> Depending on how you label the yes/no/na optionbuttons, you may have to play
> with that order.
>
> Then I hid column A -- just so it wouldn't be noticeable. But you could use any
> column you want and hide that column later.
>
> danielle wrote:
> >
> > I'm new with working with forms and macros and I'm a little lost. This is
> > what I have...
> >
> > A B C
> > 3
> > 2
> > 5 0 0
> >
> > This is what I need. I need to place 3 checkboxes each in B2, B3 and B4,
> > equally Yes,No and N/A. If yes is clicked, then B2 and C2 will populate with
> > value "3". If no is clicked, then B2 and C2 will populate with value "0". If
> > N/A is clicked then, then B2 and C2 will populate with "-".
> >
> > Is this possible? And if so, is there a macro that I can place to do all of
> > this? How would I do that macro?
> >
> > I'm sorry I'm so dense.....LOL!!
> >
> > Thank you in advance!
>
> --
>
> Dave Peterson
>

Put the formula in a cell in column C.

Are you sure you'd want to? What happens if the user clicks the wrong one and
wants to correct it?

danielle wrote:
>
> How do I get my values to show up in in my C column? And is there a way to
> have the checkboxes/option buttons to hide or disappear once they're checked?
>
> "Dave Peterson" wrote:
>
> > You might want to reconsider and use optionbuttons. Then when you choose one of
> > them, the other two are not chosen.
> >
> > I put a GroupBox from the Forms toolbar around B2:B4
> > Then I added 3 optionbuttons to that GroupBox (yes, no, na)
> > Then I rightclicked on an optionbutton
> > selected format control|On the control tab, I gave it a nice cell link.
> > (I used A3 in my test.)
> >
> > Then I used this formula to show the text.
> > =IF(A3="","",CHOOSE(A3,"Yes","N/A","-"))
> >
> > Depending on how you label the yes/no/na optionbuttons, you may have to play
> > with that order.
> >
> > Then I hid column A -- just so it wouldn't be noticeable. But you could use any
> > column you want and hide that column later.
> >
> > danielle wrote:
> > >
> > > I'm new with working with forms and macros and I'm a little lost. This is
> > > what I have...
> > >
> > > A B C
> > > 3
> > > 2
> > > 5 0 0
> > >
> > > This is what I need. I need to place 3 checkboxes each in B2, B3 and B4,
> > > equally Yes,No and N/A. If yes is clicked, then B2 and C2 will populate with
> > > value "3". If no is clicked, then B2 and C2 will populate with value "0". If
> > > N/A is clicked then, then B2 and C2 will populate with "-".
> > >
> > > Is this possible? And if so, is there a macro that I can place to do all of
> > > this? How would I do that macro?
> > >
> > > I'm sorry I'm so dense.....LOL!!
> > >
> > > Thank you in advance!
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

I need the boxes to disappear once it's clicked in order to show the value.
The spreadhseet that I'm trying to create is based on a monitoring program
were I am creating at work. The yes, no and n/a options are the only options:
Either the rep did it, didn't do it, or the question wasnt applicable.

Is this the formula Im putting in column C:
=IF(A3="","",CHOOSE(A3,"Yes","N/A","-"))

What do I need to change because it's not working.

I'm sorry!

"Dave Peterson" wrote:

> Put the formula in a cell in column C.
>
> Are you sure you'd want to? What happens if the user clicks the wrong one and
> wants to correct it?
>
> danielle wrote:
> >
> > How do I get my values to show up in in my C column? And is there a way to
> > have the checkboxes/option buttons to hide or disappear once they're checked?
> >
> > "Dave Peterson" wrote:
> >
> > > You might want to reconsider and use optionbuttons. Then when you choose one of
> > > them, the other two are not chosen.
> > >
> > > I put a GroupBox from the Forms toolbar around B2:B4
> > > Then I added 3 optionbuttons to that GroupBox (yes, no, na)
> > > Then I rightclicked on an optionbutton
> > > selected format control|On the control tab, I gave it a nice cell link.
> > > (I used A3 in my test.)
> > >
> > > Then I used this formula to show the text.
> > > =IF(A3="","",CHOOSE(A3,"Yes","N/A","-"))
> > >
> > > Depending on how you label the yes/no/na optionbuttons, you may have to play
> > > with that order.
> > >
> > > Then I hid column A -- just so it wouldn't be noticeable. But you could use any
> > > column you want and hide that column later.
> > >
> > > danielle wrote:
> > > >
> > > > I'm new with working with forms and macros and I'm a little lost. This is
> > > > what I have...
> > > >
> > > > A B C
> > > > 3
> > > > 2
> > > > 5 0 0
> > > >
> > > > This is what I need. I need to place 3 checkboxes each in B2, B3 and B4,
> > > > equally Yes,No and N/A. If yes is clicked, then B2 and C2 will populate with
> > > > value "3". If no is clicked, then B2 and C2 will populate with value "0". If
> > > > N/A is clicked then, then B2 and C2 will populate with "-".
> > > >
> > > > Is this possible? And if so, is there a macro that I can place to do all of
> > > > this? How would I do that macro?
> > > >
> > > > I'm sorry I'm so dense.....LOL!!
> > > >
> > > > Thank you in advance!
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Did you do the linked cell stuff? What cell did you use?

I used A3 in my example. Change the formula accordingly.

Assign this macro to each of the optionbuttons (all 3):

Option Explicit
Sub HideOptButtons()

Dim myOptBtn As OptionButton
Dim OptBtn As OptionButton

Set myOptBtn = ActiveSheet.OptionButtons(Application.Caller)

myOptBtn.GroupBox.Visible = False
For Each OptBtn In ActiveSheet.OptionButtons
If OptBtn.GroupBox.Name = myOptBtn.GroupBox.Name Then
OptBtn.Visible = False
End If
Next OptBtn

End Sub

In fact, you can drop the linked cell and just use code for everything. This
takes the caption and puts it in C3. Adjust if necessary.

Option Explicit
Sub testme()

Dim myOptBtn As OptionButton
Dim OptBtn As OptionButton
Dim myCell As Range

Set myCell = ActiveSheet.Range("c3")

Set myOptBtn = ActiveSheet.OptionButtons(Application.Caller)

myOptBtn.GroupBox.Visible = False
For Each OptBtn In ActiveSheet.OptionButtons
If OptBtn.GroupBox.Name = myOptBtn.GroupBox.Name Then
OptBtn.Visible = False
If OptBtn.Value = xlOn Then
myCell.Value = OptBtn.Caption
End If
End If
Next OptBtn

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




danielle wrote:
>
> I need the boxes to disappear once it's clicked in order to show the value.
> The spreadhseet that I'm trying to create is based on a monitoring program
> were I am creating at work. The yes, no and n/a options are the only options:
> Either the rep did it, didn't do it, or the question wasnt applicable.
>
> Is this the formula Im putting in column C:
> =IF(A3="","",CHOOSE(A3,"Yes","N/A","-"))
>
> What do I need to change because it's not working.
>
> I'm sorry!
>
> "Dave Peterson" wrote:
>
> > Put the formula in a cell in column C.
> >
> > Are you sure you'd want to? What happens if the user clicks the wrong one and
> > wants to correct it?
> >
> > danielle wrote:
> > >
> > > How do I get my values to show up in in my C column? And is there a way to
> > > have the checkboxes/option buttons to hide or disappear once they're checked?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You might want to reconsider and use optionbuttons. Then when you choose one of
> > > > them, the other two are not chosen.
> > > >
> > > > I put a GroupBox from the Forms toolbar around B2:B4
> > > > Then I added 3 optionbuttons to that GroupBox (yes, no, na)
> > > > Then I rightclicked on an optionbutton
> > > > selected format control|On the control tab, I gave it a nice cell link.
> > > > (I used A3 in my test.)
> > > >
> > > > Then I used this formula to show the text.
> > > > =IF(A3="","",CHOOSE(A3,"Yes","N/A","-"))
> > > >
> > > > Depending on how you label the yes/no/na optionbuttons, you may have to play
> > > > with that order.
> > > >
> > > > Then I hid column A -- just so it wouldn't be noticeable. But you could use any
> > > > column you want and hide that column later.
> > > >
> > > > danielle wrote:
> > > > >
> > > > > I'm new with working with forms and macros and I'm a little lost. This is
> > > > > what I have...
> > > > >
> > > > > A B C
> > > > > 3
> > > > > 2
> > > > > 5 0 0
> > > > >
> > > > > This is what I need. I need to place 3 checkboxes each in B2, B3 and B4,
> > > > > equally Yes,No and N/A. If yes is clicked, then B2 and C2 will populate with
> > > > > value "3". If no is clicked, then B2 and C2 will populate with value "0". If
> > > > > N/A is clicked then, then B2 and C2 will populate with "-".
> > > > >
> > > > > Is this possible? And if so, is there a macro that I can place to do all of
> > > > > this? How would I do that macro?
> > > > >
> > > > > I'm sorry I'm so dense.....LOL!!
> > > > >
> > > > > Thank you in advance!
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson