Option Button Properties Change Based On A Checkbox
Jun 13, 2006
I would like to set up some option buttons so that the will be become visible and enabled when a check box is checked, and the opposite when the same checkbox is not check. This is my problem
Private Sub cbpDiscAlum_Click()
Dim myOption As Control
Dim myValue As Boolean
myValue = cbpDiscAlum.Value = True
If myValue = True Then
For Each myOption In pDiscounts.Controls
myOption.Visble = True
myOption.Enabled = True
Next myOption
Else
For Each myOption In pDiscounts.Controls
myOption.Visible = False
myOption.Enabled = False
Next myOption
End If
End Sub
It is the 5th command where it gets hung up: For Each myOption In pDiscounts.Controls. I am sure I am spelling both of the names correctly: the checkbox, and the group name.
I am trying to use VBA to change the caption of checkboxes in "Sheet 2" when I change the value of a cell "A1" in "Sheet 1".
This code is working:
Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
Worksheets("Sheet 2").CheckBox1.Caption = "New Caption"End Sub
But there are 6 checkboxes in Sheet 2 and I would like to do something like this:
Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub For i = 0 to 5Worksheets("Sheet 2").Control("CheckBox" & i+1).Caption = "Box" & i+1Next iEnd Sub
This doesn't work.. So I guess the Control-function is wrong.
I have a button wich will add data entered on a form to a spreadsheet if the data meets certain criteria. If it does not it will not let you add anything. I would like to add a second button that will add the data even if it does not meet the criteria but I need the second button to only be enabled with a password. Maybe a macro to change the button properties from locked to unlocked (true to false).
I have a series of 12 option buttons on my spreadsheet named Opt_1 thro' opt_12. Whenever one is pressed, I want to invoke code to action the following:
change forecolor of non-selected option buttons to yellow change forecolor of the selected option button to red Enter in Cell A1 the number of the option button slected
So, for example, if Opt_4 was pressed, then
Opt_1 - Opt_3 & Opt_5 - Opt_12 forecolor = yellow Opt_4 forecolor = red Cell A1 gets the value of 4 entered into it.
I know how to do this on a user form, by putting the option buttons in a frame and then picking up the array value of the option button, but I have these directly on my spreadsheet next to 12 cells as this is more relevant to my application.
I thought of a Select - Case statement, but this will be rather lengthy. Have you any suggestions on how to tighten this code down.
I'm a complete Excel and VBA newbie and would like to know if it's possible to change the output of OptionButtons from 'TRUE' or 'FALSE' to "1"/"2"/"3"/etc..
The name of each button is:
Rating 1 Rating 2 Rating 3 Rating 4 and so forth..
The idea is for each rating to output its assigned number such that if the user were to click "Rating 1", the result would be "1", "Rating 2" = 2 and so on. I'm trying to design a survey and these results will then be used to calculate an average rating. If this is possible, how would I need to write the code for it?
I'm trying to use option buttons to create a text entry in an adjacent cell. Selecting Option Button 1 puts "GPM" in cell F23. Selecting Option Button 1 puts "lbm/hr" in cell F23 (The option buttons are from the control toolbox)
I receive ""Run-time error '424': Object Required"" and first line is highlighted- here is copy of
Sub FlowUnits() If OptionButton1.Value = True Then Sheets("Main Screen").Range("F23") = "GPM" End If If OptionButton2.Value = True Then Sheets("Main Screen").Range("F23") = "lbm/hr" End If End Sub
i have a user form in my spreadsheet that uses option buttons and text boxes for user entry. i need to take the values and true false entries from the option buttons and place them in cells. i am alittle lost with this.
How do I get the listboxes to pre-populate based on the Userid and Option Button selection? Furthermore, write back to the correct cell once the selection has been made?
How can I get the combobox to remove blanks in the Userid? (if each cell in row2 has to be populated then so be it)
Is there a way to set up a "watch" on a cell so that if you type in a different number on a cell, OptionButton1_Click() gets activated? Example:
A9 = 12.0104 OptionButton2 is active.
In Cell A9 you type "25.0508". OPtionButton2 becomes inactive and OptionButton1 becomes active. I already have the buttons linked, i just do not know how to make the button get triggered if A9 changes.
I'm using excel 2003 and received a file that has the properties (file/Properties) menu option grayed out. I need to access the properties menu to identify file information.
I have a frame (Frame1) on a userform added using Microsoft Forms 2.0 Frame. I have added option buttons to the frame named OptionButton1 thru OptionButton4. I am trying to add code where certain cells are copied and pasted depending on which optbutton is selected. I tried the following code but because the option button is a frame object it doesn't seem to trigger the event.
Private Sub OptionButton1_Click()
'copy level 1 If Me.OptionButton1 = True Then Worksheets("Sheet1").Range("G10:G32").Copy Worksheets("Sheet1").Select Worksheets("Sheet1").Range("C10:C32").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False End If
I currently have a spreadsheet with a userform that has multiple checkboxes. I want these checkboxes to change the value of cells depending on whether or not they are checked. 1 for not checked, 2 for checked. The column of the named range the cells are in is constant for each checkbox (though it is different for each checkbox and some checkboxes will need to change the value of a cell in multiple columns). The row changes depending on the value of a combobox on the userform. I have sorted out a code that works, but it seems very long, slow, and inefficient. I'm hopping someone can show me a better way to do it.
Private Sub CmbFinish_Click() Dim rClSkills As Range Dim rClLookup As Range Dim var1 As Integer Dim iDecision As Integer Set rClSkills = Range("TblClSkills") Set rClLookup = Range("ClassLookup") iDecision = MsgBox("Are you sure you wish to change Skills?", vbYesNo, "Continue?") If iDecision = vbYes Then var1 = Application.WorksheetFunction.Match(cboClass.Value, rClLookup, 0) If CboAppraise.Value = True Then.............................
Im trying to create many option buttons on my sheet and then change the GroupName and Name properties but im having no luck in accessing the properties. Here is the code ive got at the moment, could someone tell me how to access these properties. Shown below is a small section of the code, if you require the full code i will happily provide it. This is my first post and im not sure if this code tag thing has been done properly:
n = 18 Call Section_Header Range("D" & n - 1).Value = " Case" For i = 1 To UBound(Name_Case, 1) Range("D" & n + i).Value = Name_Case(i) Range("M" & n + i).Value = Cost_Case(i) ActiveSheet. OLEObjects.Add(ClassType:="Forms.OptionButton.1", _ Left:=628, Top:=(n - 1) * 15.75 + (1 * 6) + (i - 1) * 15.75, Width:=46.5, Height _ :=15.75).Select Next i
Sub OptionButton222_Click() With Worksheets("Beam Input") .Shapes("Notch 1").Visible = False .Shapes("Notch 2").Visible = False End With End Sub Sub OptionButton223_Click() With Worksheets("Beam Input") .Shapes("Notch 1").Visible = False .Shapes("Notch 2").Visible = True End With End Sub Sub OptionButton224_Click() With Worksheets("Beam Input") .Shapes("Notch 1").Visible = True .Shapes("Notch 2").Visible = False End With End Sub
i need to modify it so that a selection on the beam input page causes the images to change on the beam output page. Currently this code is in the module section of VBA.
I have a button on a worksheet that activates a macro, the macro is stored in a "personal.xlsb" file. This file is copied to several users computers so they can use the macro, problem is once the button is assigned to a macro from one computer all the other users can't use the macro. What can I do to make this macro work on all computers? (Less placing yet another button on the tool bar).
I am trying to change the text that appears on a button on a worksheet via a macro. There is only one button on the worksheet so in the macro I use
MyCaption = activecell.value 'Pick up the button text from the spreadsheet For each sh in activesheet.shapes sh.caption = MyCaption 'I know this would do all, but only one button as I said next sh
which generates "Object doesn't support this property or method".
This is strange because if you get the properties box up, Caption definitely shows as a property of a button control.
I have managed to create a command button in excel 2007 that performs a macro upon a click. But, after re-opening the workbook, I am unable to right click on the button to recall the properties window.
I've got a drop-down menu with four options in it that match up with the cells D11:D14, and next to those I've got spots for counting the totals (E11:E14). Next to the drop-down I've got plus and minus buttons, and I need to make it so if I have the top option selected in the dropdown and press plus, it adds +1 to E11. Second option and the same plus button, +1 to E12, and so on.
The purpose is for a call tracker, the employee would select the reason for the call (maybe more added later) and hit the button to add the call to the report.
I've looked so I know you normally ask for what I've got so far, and that's nothing. I've got all the other buttons wired up but I don't even know where to start with this one. I can include a screenshot if necessary.
I am having a hard time setting the MultiLine, Scrollbars and WordWrap properties through programmation. Actually what I want my code to do is to create a set of textboxes in my spreadsheet and then set the MultiLine, Scrollbars and WordWrap properties. Right now I can create the boxes and rename them but I'm unable to change the other properties.
i am trying to use a combo box to change the properties of a text box. i think i can do this by having it look at the cell link that i sent and then changing the text box appropriately with something like this:
It has been awhile since I've worked on anything and I can't remember how to do something that is probably very simple. I have placed an option button on my worksheet and cannot for the life of me remember how to change the properties. When I right click and select properties a box pops up but there is no info there. I want to be able to change the name and group etc.
I have a question regarding the properties of an Excel file (.xls 2003). I can change them all (as indicated on Microsoft support website) expect the name of the last person who recorded the document (File ==> Properties==>Stat.)
I'm trying to replace a bunch of checkboxes on a sheet with a bunch of Form Control Option Buttons. The problem I'm having is every option button I add seems to be grouped with all of the rest of my option buttons. If I add buttons 1 & 2, I need those 2 grouped together, and when I add 3 & 4, I need those grouped together. Currently it's grouping all 4 buttons together so 1 - 4 are linked, but I need 1 & 2 linked together, and 3 & 4 linked together. I've tried grouping, but it still doesn't function how it ought to.
I have a worksheet where the user selects between two option controls, which are mutually exclusive. when they toggle an option, a user form pops up, depending on which option they selected, and askes them for a number, which is used in a calculation elsewhere in the page. The user form has a cancel button, in case they decide to back out. If they cancel, I want the option buttons to reset back to their original state.
The problem I'm having is that when I code it, if you select cancel on the user form, it causes the option to revert back to it's previous state, but that triggers the selection of the option again, which pops up the form, making a neverending loop! Is there a better way to do this? I tried it with a toggle button, with the same results.
