| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to save the option button value
Hi,
I have a worksheet, depending on different choice in a dropdown list, corresponding range which including a couple of option buttons should be filled. Here is an code sample: If Worksheets("Sheet1").Range("E5").Value = "1" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("E8:F10") ' enable the option buttons in the range(E8:F10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj Else If Worksheets("Sheet1").Range("E5").Value = "2" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("G8:H10") ' enable the option buttons in the range(G8:H10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj End if End if The basic idea of this code is: first, set the value of each option button in the worksheet to false and disable all of them; then, based on different choice in dropdown list, option buttons in specific range enable. After I ticked option buttons in specific range, I saved this worksheet. However, I encountered one problem when I open this saved worksheet. All the value of option buttons in the specific range changed to false, no matter what I chose before save. I want to get the worksheet with option button value saved. Do you have some ideas to fix this problem? Thank you in advance!!! Best Regards Sam |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to save the option button value
Link the buttons to cells, separate cells, then it will pick up those values
next time. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message ups.com... Hi, I have a worksheet, depending on different choice in a dropdown list, corresponding range which including a couple of option buttons should be filled. Here is an code sample: If Worksheets("Sheet1").Range("E5").Value = "1" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("E8:F10") ' enable the option buttons in the range(E8:F10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj Else If Worksheets("Sheet1").Range("E5").Value = "2" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("G8:H10") ' enable the option buttons in the range(G8:H10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj End if End if The basic idea of this code is: first, set the value of each option button in the worksheet to false and disable all of them; then, based on different choice in dropdown list, option buttons in specific range enable. After I ticked option buttons in specific range, I saved this worksheet. However, I encountered one problem when I open this saved worksheet. All the value of option buttons in the specific range changed to false, no matter what I chose before save. I want to get the worksheet with option button value saved. Do you have some ideas to fix this problem? Thank you in advance!!! Best Regards Sam |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to save the option button value
Hi,
Could you explain more clearly? Thank you! The option buttons I used are from control toolbox. The option button are groups of Yes or No choice. I just want to record the yes or no info. If I choose yes, the value of that specific option button will be ture. You mean I can link the buttons to some cell. You mean using cells to record related info? Best Regards Sam Bob Phillips wrote: Link the buttons to cells, separate cells, then it will pick up those values next time. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message ups.com... Hi, I have a worksheet, depending on different choice in a dropdown list, corresponding range which including a couple of option buttons should be filled. Here is an code sample: If Worksheets("Sheet1").Range("E5").Value = "1" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("E8:F10") ' enable the option buttons in the range(E8:F10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj Else If Worksheets("Sheet1").Range("E5").Value = "2" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("G8:H10") ' enable the option buttons in the range(G8:H10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj End if End if The basic idea of this code is: first, set the value of each option button in the worksheet to false and disable all of them; then, based on different choice in dropdown list, option buttons in specific range enable. After I ticked option buttons in specific range, I saved this worksheet. However, I encountered one problem when I open this saved worksheet. All the value of option buttons in the specific range changed to false, no matter what I chose before save. I want to get the worksheet with option button value saved. Do you have some ideas to fix this problem? Thank you in advance!!! Best Regards Sam |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to save the option button value
If you go into design mode, the blue-green triangle on the controls toolbox
toolbar, then double click the option button. This will take you to the VBIDE for that object. If the properties box is not displayed, show it with F4. In the properties for the optionbutton, you will see a LinkedCell property. Link each button in this way to a separate cell, preferably a cell off-screen or in a hidden column. When either button is selected, that linked cell will get a value TRUE, others will get a value of FALSE (unless you have groups, in which case the related items will change). Saving the workbook saves these values, so opening it will re-set the option buttons. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message oups.com... Hi, Could you explain more clearly? Thank you! The option buttons I used are from control toolbox. The option button are groups of Yes or No choice. I just want to record the yes or no info. If I choose yes, the value of that specific option button will be ture. You mean I can link the buttons to some cell. You mean using cells to record related info? Best Regards Sam Bob Phillips wrote: Link the buttons to cells, separate cells, then it will pick up those values next time. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message ups.com... Hi, I have a worksheet, depending on different choice in a dropdown list, corresponding range which including a couple of option buttons should be filled. Here is an code sample: If Worksheets("Sheet1").Range("E5").Value = "1" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("E8:F10") ' enable the option buttons in the range(E8:F10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj Else If Worksheets("Sheet1").Range("E5").Value = "2" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("G8:H10") ' enable the option buttons in the range(G8:H10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj End if End if The basic idea of this code is: first, set the value of each option button in the worksheet to false and disable all of them; then, based on different choice in dropdown list, option buttons in specific range enable. After I ticked option buttons in specific range, I saved this worksheet. However, I encountered one problem when I open this saved worksheet. All the value of option buttons in the specific range changed to false, no matter what I chose before save. I want to get the worksheet with option button value saved. Do you have some ideas to fix this problem? Thank you in advance!!! Best Regards Sam |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to save the option button value
Hi
I think we misunderstand each other, we talked at cross-purposes here :-) I got your ideas. Anyway,thank you for this idea. However, My idea is to make the option button choice visible to users directly. It is not to record the info in some other cells. I want to find some approach try to avoid the value of option button to reset when I open the saved option button choice again. Do you have some suggestions? I am wondering whether it is possible to add some code to prevent the value of option button to reset. Best Regards Shang Bob Phillips wrote: If you go into design mode, the blue-green triangle on the controls toolbox toolbar, then double click the option button. This will take you to the VBIDE for that object. If the properties box is not displayed, show it with F4. In the properties for the optionbutton, you will see a LinkedCell property. Link each button in this way to a separate cell, preferably a cell off-screen or in a hidden column. When either button is selected, that linked cell will get a value TRUE, others will get a value of FALSE (unless you have groups, in which case the related items will change). Saving the workbook saves these values, so opening it will re-set the option buttons. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message oups.com... Hi, Could you explain more clearly? Thank you! The option buttons I used are from control toolbox. The option button are groups of Yes or No choice. I just want to record the yes or no info. If I choose yes, the value of that specific option button will be ture. You mean I can link the buttons to some cell. You mean using cells to record related info? Best Regards Sam Bob Phillips wrote: Link the buttons to cells, separate cells, then it will pick up those values next time. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message ups.com... Hi, I have a worksheet, depending on different choice in a dropdown list, corresponding range which including a couple of option buttons should be filled. Here is an code sample: If Worksheets("Sheet1").Range("E5").Value = "1" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("E8:F10") ' enable the option buttons in the range(E8:F10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj Else If Worksheets("Sheet1").Range("E5").Value = "2" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("G8:H10") ' enable the option buttons in the range(G8:H10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj End if End if The basic idea of this code is: first, set the value of each option button in the worksheet to false and disable all of them; then, based on different choice in dropdown list, option buttons in specific range enable. After I ticked option buttons in specific range, I saved this worksheet. However, I encountered one problem when I open this saved worksheet. All the value of option buttons in the specific range changed to false, no matter what I chose before save. I want to get the worksheet with option button value saved. Do you have some ideas to fix this problem? Thank you in advance!!! Best Regards Sam |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to save the option button value
Sam,
If you are closing the userform and then displaying again it will not have saved the previous values in order to "reset" them. It only holds these in memory while the form is displayed. What you would need to do as suggested is to store the 'previous' value in a cell (on hidden sheet would be best) so that when you reload you can restore the previous values. Duncan sam wrote: Hi I think we misunderstand each other, we talked at cross-purposes here :-) I got your ideas. Anyway,thank you for this idea. However, My idea is to make the option button choice visible to users directly. It is not to record the info in some other cells. I want to find some approach try to avoid the value of option button to reset when I open the saved option button choice again. Do you have some suggestions? I am wondering whether it is possible to add some code to prevent the value of option button to reset. Best Regards Shang Bob Phillips wrote: If you go into design mode, the blue-green triangle on the controls toolbox toolbar, then double click the option button. This will take you to the VBIDE for that object. If the properties box is not displayed, show it with F4. In the properties for the optionbutton, you will see a LinkedCell property. Link each button in this way to a separate cell, preferably a cell off-screen or in a hidden column. When either button is selected, that linked cell will get a value TRUE, others will get a value of FALSE (unless you have groups, in which case the related items will change). Saving the workbook saves these values, so opening it will re-set the option buttons. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message oups.com... Hi, Could you explain more clearly? Thank you! The option buttons I used are from control toolbox. The option button are groups of Yes or No choice. I just want to record the yes or no info. If I choose yes, the value of that specific option button will be ture. You mean I can link the buttons to some cell. You mean using cells to record related info? Best Regards Sam Bob Phillips wrote: Link the buttons to cells, separate cells, then it will pick up those values next time. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message ups.com... Hi, I have a worksheet, depending on different choice in a dropdown list, corresponding range which including a couple of option buttons should be filled. Here is an code sample: If Worksheets("Sheet1").Range("E5").Value = "1" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("E8:F10") ' enable the option buttons in the range(E8:F10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj Else If Worksheets("Sheet1").Range("E5").Value = "2" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("G8:H10") ' enable the option buttons in the range(G8:H10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj End if End if The basic idea of this code is: first, set the value of each option button in the worksheet to false and disable all of them; then, based on different choice in dropdown list, option buttons in specific range enable. After I ticked option buttons in specific range, I saved this worksheet. However, I encountered one problem when I open this saved worksheet. All the value of option buttons in the specific range changed to false, no matter what I chose before save. I want to get the worksheet with option button value saved. Do you have some ideas to fix this problem? Thank you in advance!!! Best Regards Sam |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to save the option button value
That is what I gave you. Try it and see.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message ups.com... Hi I think we misunderstand each other, we talked at cross-purposes here :-) I got your ideas. Anyway,thank you for this idea. However, My idea is to make the option button choice visible to users directly. It is not to record the info in some other cells. I want to find some approach try to avoid the value of option button to reset when I open the saved option button choice again. Do you have some suggestions? I am wondering whether it is possible to add some code to prevent the value of option button to reset. Best Regards Shang Bob Phillips wrote: If you go into design mode, the blue-green triangle on the controls toolbox toolbar, then double click the option button. This will take you to the VBIDE for that object. If the properties box is not displayed, show it with F4. In the properties for the optionbutton, you will see a LinkedCell property. Link each button in this way to a separate cell, preferably a cell off-screen or in a hidden column. When either button is selected, that linked cell will get a value TRUE, others will get a value of FALSE (unless you have groups, in which case the related items will change). Saving the workbook saves these values, so opening it will re-set the option buttons. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message oups.com... Hi, Could you explain more clearly? Thank you! The option buttons I used are from control toolbox. The option button are groups of Yes or No choice. I just want to record the yes or no info. If I choose yes, the value of that specific option button will be ture. You mean I can link the buttons to some cell. You mean using cells to record related info? Best Regards Sam Bob Phillips wrote: Link the buttons to cells, separate cells, then it will pick up those values next time. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sam" wrote in message ups.com... Hi, I have a worksheet, depending on different choice in a dropdown list, corresponding range which including a couple of option buttons should be filled. Here is an code sample: If Worksheets("Sheet1").Range("E5").Value = "1" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("E8:F10") ' enable the option buttons in the range(E8:F10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj Else If Worksheets("Sheet1").Range("E5").Value = "2" Then Set rng = Worksheets("Sheet1").Range("E8:H10") ' Set all the value of option buttons in this range to false ' and disable all the option buttons For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then obj.Object.Value = False obj.Object.Enabled = False End If End If Next obj Set rng1 = Worksheets("Sheet1").Range("G8:H10") ' enable the option buttons in the range(G8:H10) For Each obj In Worksheets("Sheet1").OLEObjects If TypeOf obj.Object Is MSForms.OptionButton Then If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then obj.Object.Enabled = True End If End If Next obj End if End if The basic idea of this code is: first, set the value of each option button in the worksheet to false and disable all of them; then, based on different choice in dropdown list, option buttons in specific range enable. After I ticked option buttons in specific range, I saved this worksheet. However, I encountered one problem when I open this saved worksheet. All the value of option buttons in the specific range changed to false, no matter what I chose before save. I want to get the worksheet with option button value saved. Do you have some ideas to fix this problem? Thank you in advance!!! Best Regards Sam |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Save as .xla option is not coming when I try to save | Excel Discussion (Misc queries) | |||
| Excel 2002 has no option to add the save as button to the toolbar- | Excel Discussion (Misc queries) | |||
| How to diasble save and save as menu but allow a save button | Excel Programming | |||
| how to get disk icon on save button of save as dialog like 2000 | Excel Discussion (Misc queries) | |||
| keep source formatting is not an option in paste option button | Excel Discussion (Misc queries) | |||
![sam[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
