Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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 pictu 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 |
#3
|
|||
|
|||
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 pictu 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros to delete check boxes | Excel Discussion (Misc queries) | |||
Excel won't copy outside sheet | Excel Discussion (Misc queries) |