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.

View 3 Replies


ADVERTISEMENT

How To Change The Properties Of A CheckBox Using VBA

Aug 4, 2014

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.

View 3 Replies View Related

Change Form Button Properties With A Macro?

Nov 12, 2008

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).

View 2 Replies View Related

Change Cell Color By Option Button

Nov 22, 2006

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.

View 3 Replies View Related

Change Caption Property Of Option Button

Mar 23, 2007

I have a problem with the changing the caption property of the option buttons to bold, italic and regular.

Is it some vb code you need to put in for the option buttons?

View 8 Replies View Related

Change Output Of Option Button To Caption Or Name

Jan 11, 2008

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?

View 2 Replies View Related

Value To Cell Based On Option Button

Dec 29, 2007

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

View 3 Replies View Related

Return Yes/No To Cell Based On Option Button

Aug 31, 2007

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.

View 6 Replies View Related

Preselect Listboxes Based On Option Button Selection?

Apr 24, 2014

see the attached below.

A few things to sort if I may

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)

ToolMatrix3.xlsm

View 2 Replies View Related

Automatically Check Option Button Based On Cell Value

Jan 24, 2008

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.

View 8 Replies View Related

Disabled Properties Menu Option

Apr 9, 2009

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.

View 6 Replies View Related

Copy Range As Paste As Values Based On UserForm Option Button Choice

Nov 3, 2009

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

End Sub

View 4 Replies View Related

Checkbox To Change Cell Value Based On Other Checkboxes

Jun 4, 2008

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.............................

View 6 Replies View Related

Changing Groupname And Other Properties In Option Buttons From The Control Toolbox

May 31, 2006

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

View 2 Replies View Related

Option Button On Sheet 1 To Change Image On Sheet 2

Jan 15, 2007

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.

View 2 Replies View Related

Set Worksheet Button Properties

Oct 28, 2008

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).

View 2 Replies View Related

Controlling Button Properties On A Worksheet

Nov 2, 2007

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.

View 9 Replies View Related

Excel 2007 :: Command Button Properties

Apr 19, 2014

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.

View 2 Replies View Related

Adding A Command Button And Then Editing Its Properties Programatically

Apr 25, 2007

I'm adding a command button at run time using excels help code;

Worksheets(newWorksheetName). OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=325, Top:=24, Width:=50, Height:=20

I want to change the buttons properties including its caption etc.. I've tried using'

Worksheet(newWorksheetName).OLEObjects("CommandButton1").Caption = "Do"

But i get a run time error. Also, the add method doesn't specify what the button is called, any clues?

View 2 Replies View Related

Change Button Function Based On Dropdown Menu Selection?

Jun 9, 2014

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.

View 5 Replies View Related

Change Textbox Properties

May 25, 2007

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.

inst_num = Range("AA1").Value

ActiveSheet. OLEObjects.Add("Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=20, Top:=95 + (inst_num - 1) * 105, Width:=70, Height _
:=30).Select

ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=100, Top:=95 + (inst_num - 1) * 105, Width:=100, Height _
:=30).Select.............

View 2 Replies View Related

Combo Box To Change The Properties Of A Text Box

Oct 9, 2009

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:

View 3 Replies View Related

Can't Change Combobox Properties (activex)

Apr 24, 2009

How do I set the font size property and special effect property in this code?

I was trying to answer a question on Experrt Exchange but Rory beat me to it and now I'm feeling frustrated that I can't get my solution to work.

Sub AddCombo()Dim rVals As Range, rCell As Range, lTop, lLef, lHeight, lWidth, lCount As LongSet rVals = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)lCount = 1For Each rCell In rVals    If rCell.Validation.Type = 3 Then        lTop = rCell.Top        lLeft = rCell.Left        lHeight = rCell.Rows.Height        lWidth = rCell.Columns.Width            With ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight)                .Name = "NewCombo" & lCount                .ListFillRange = rCell.Validation.Formula1                .LinkedCell = rCell.Address(0, 0)                '.SpecialEffect = fmSpecialEffectFlat //doesn't work //                '.FontSize = 14 //doesn't work //                '.Font.Size = 14 // doesn't work //            End With                        lCount = lCount + 1    End IfNext rCellEnd Sub

View 9 Replies View Related

Set An Option Button By Name

Oct 8, 2009

In Excel 2003 VBA, what is the syntax for controling an Option Button or Checkbox by name or caption

I have tried the following but has an error, I am sure this is a simple one.

View 4 Replies View Related

Excel 2010 :: Change ActiveX Properties

May 29, 2013

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.

View 3 Replies View Related

Excel 2003 :: How To Change Properties Of File

Jun 1, 2013

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.)

How to change the name?

View 2 Replies View Related

VBA - Loop Through All Images On Sheet Change Properties

Jun 18, 2013

I need some code that will loop through all the images on a spreadsheet, and change the properties so the images will "Move and Size with Cells"

View 2 Replies View Related

Option Button Grouping

Dec 10, 2013

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.

View 13 Replies View Related

Option Button To Post Value

Mar 18, 2009

Not sure how too do this, here is a simple example of what I want too do. Just using different buttons to post a number.

View 6 Replies View Related

Option Button Usage

Jan 2, 2010

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.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved