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


ADVERTISEMENT

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

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

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

Macro To Change Table Properties In Outlook 2010

Jun 16, 2013

The macro im using copys a table from excel into a email. Unfortunatly the email is not readable for blackberry users due to the table properties.

I need to change a few properies. This is what i do manually.....

In outlook i then hover over the table right click then select "Distribute rows evenly" then i right click again but this time i select "Table properties" i then go to the "Row" Tab

i then untick the "allow row to break across pages"

then tick "specify height" and change the value to 0.4 cm and finally change "row height is" to "exactly"

Below is the whole code im using so far....

Code:
Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object

[Code]...

View 2 Replies View Related

Change Properties Of Controls On Ribbon Toolbar. 2007

Feb 26, 2008

I've created a custom ribbon which contains checkboxes that hide or show columns.

I would like to change the enable state of the checkboxes while the user is interacting with the spreadsheet.

For example:I would like to disable all of the checkboxes if they are not viewing sheet1I would like to disable the second checkbox if the first checkbox is unchecked.

I know how to set these all on initialize. I just have not figured out how to update these controls after it has been opened.

View 4 Replies View Related

Change Values Of ActiveX Checkbox Within VBA

Jun 3, 2014

I have a sheet with many active x check boxes already created but with visibility off. I want code that turns on the visibility, repositions, and checks certain check boxes whose name is based on values in an array named configs.

Here is the code with the problem line indicated:

[Code] ........

The repositioning and visibility work fine. I get an "object doesn't support property or method" error on the indicated line. What is the proper syntax to do this?

View 4 Replies View Related

Increment Through Checkbox To Check Status And Change A Value?

May 1, 2014

I have a userform that has 28 checkbox's that we go through that we check which ones apply to a report.. To keep those boxes checked as the supervisor's cycle through the reports, I am leaving a 28 digit code next to the report which is created as they check/uncheck the proper checkbox's.

[Code].....

So if say CheckBox10.value = True, then the 10th digit of the ID will change to 1 while keeping the rest of the ID the same.

View 6 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

Excel 2010 :: Change Font Size In A Checkbox?

Jan 30, 2013

How do you changethe font size in acheckbox in excel 2010?

View 1 Replies View Related

Change Chart Axis Font Color With A Checkbox

Aug 22, 2014

Any way to change the font color of a chart axis when a checkbox is set to true.

let checkbox be checkbox5 and chart be chart5. What I would like to happen is that if checkbox5 is true then the font color of the horizontal axis changed to grey (RGB 166,166,166), if it is false then I would like the font color of the horizontal axis to be black (RGB 0,0,0). I also would like this macro to be run every time the checkbox changes, or the corresponding cell changes to TRUE or FALSE (it is in another sheet, say sheet2). This is the code I have so far but all it is doing is showing the chart as selected and not changing the font color. I would also like it to not physically select the chart, this is for a dashboard so the selecting of the chart throws off some of the visuals a bit, but not sure how to do change the chart values without selecting it.

[Code] ......

View 7 Replies View Related

Change Value Of A Cell With Checkbox And Return It Back To Original Value?

Nov 29, 2011

I'm trying to make cell values in a range change so they appear blank when a checkbox is checked, and when it is unchecked, I need the original value to appear. I have the following so far:

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then Range("BTS").Value = ""

End Sub

...However I do not know how to change the cells back to the original value before it was made blank.

View 2 Replies View Related

Protect Page But Still Allow Checkbox Link To Happen (change)

Jan 25, 2012

I have a page that is protected so formulas are not changed mistakenly, and have check boxes on the paged that link to cells and display True of False based on the checking of the box or not. If I leave the page unprotected I risk the True or False being changed. Is there a lay to protect those cell from the user but still change when the box is checked or not. The checkboxes are Form Control type.

View 6 Replies View Related

Print The Worksheet On Different Paper Which Requires To Go Into The Properties And Change The Paper Source From Automatically Select To Manual Feed

Jun 30, 2006

When I hit the print button the worksheet prints on the paper in the bin. However, there are times when I need to print the worksheet on different paper which requires me to go into the properties and change the paper source from Automatically Select to Manual Feed. I have been trying to created a macro what will switch to Manual Feed, print the worksheet and then switch back to Automatically Select but have been unsucessfull.

View 3 Replies View Related

Change Form Checkbox Fill Color For ALL Checkboxes In Workbook When Checked (True)

Nov 20, 2013

I have a multi-worksheet workbook that has many forms control checkboxes throughout it. I'm looking for some VBA that will change the background (fill) color of ALL the checkbox when it is checked (True). I've seen code for a single checkbox, but not multiple/all boxes. I know just enough VBA to be dangerous, but I'm up to learning anything new.

View 3 Replies View Related

Excel 2010 :: VBA To Change Form Control Checkbox Background Color When Checked

Nov 22, 2013

I have an Excel 2010 workbook with many worksheets using hundreds of Form Control (not ActiveX) checkboxes. I need a bit of VBA to change Checkbox background color of each checkbox whenever the user checks the box. I assume this needs to be a click event? I don't even know the Checkbox property name I need to change I'm learning VBA as quick as I can

View 4 Replies View Related

Checkbox : Married, Widows, Single Checkbox Is Selected

Feb 25, 2009

married, widows, single checkbox is selected

macro solution
good work.

View 5 Replies View Related

Stop Checkbox Sub From Jumping To Other Checkbox Subs

Apr 16, 2014

I have 3 checkboxes; when one is checked, a set/range of rows should be visible. Only 1 checkbox should be checked at a time.

If checkbox 18 is already checked, and checkbox 20 is then checked, I want the first checkbox unchecked and the rows for checkbox hidden.

I'm using the following code. It works great as long as I check and uncheck the same box before attempting to check another box. But if Checkbox18 is already checked with its rows showing, and I then check checkbox20, the checkbox20 sub runs and as I step through, it jumps to sub checkbox18.

How can I stop my subs from jumping from one to another?

Code:
Private Sub CheckBox18_Click()
If CheckBox18.Value = True Then
Worksheets("TRF").Rows("36:41").Hidden = False
Worksheets("TRF").Rows("42:64").Hidden = True
Worksheets("TRF").Rows("65:76").Hidden = True
CheckBox19.Value = False

[Code] .........

View 9 Replies View Related

Using Checkbox To Change Cell Value And Lock Cell

Aug 18, 2014

I need a checkbox (actually a few but I will use the same code and change the name and cell reference) to do the following

Checkbox in A1 is ON by defaultB1 is 100User can free change the value of B1 when checkbox is onWhen checkbox is OFF"100" (or whatever number entered by the user) gets storedB1 changes to a relatively large number "9999"B1 changes cell color to grey and is locked for editWhen checkbox is ON againThe value stored previous to checkbox turned OFF is restored back to B1B1 changes back to normal cell color (no fill or white)B1 is unlocked and user can edit the cell

View 5 Replies View Related

Hide/Unhide Checkbox Using Another Checkbox

Dec 14, 2008

How can i hide and unhide one checkbox using another one? Can it be done using IF formula?
And also i am using this checkbox to function something else as TRUE/FALSE.

View 6 Replies View Related

Checkbox Vba: The Amount From The Subtotal Cell Will Auto Go To The Cell Next To The Checkbox

May 9, 2006

I have been using different keywords to find what I need. I have 3 checkboxes: cash, amex, other. I have a subtotal cell. When the appropriate checkbox is checked, the amount from the subtotal cell will auto go to the cell next to the checkbox. Is this VB?

View 6 Replies View Related

Set Properties Value

Apr 3, 2008

storedPath = .CustomDocumentProperties("PathCertString").Value

Although the question I'm about to ask is not related to Excel, but related to MS Word, the coding is similar.

The above code I used to set the properties value, but I get an error highlighting 'storedPath'. I speculate MS Word does not recognized this word. Is there another word or code that I can use to set the value in the MS Word document properties?

View 9 Replies View Related

Tab Properties

May 25, 2006

Within my code I have restricted the toolbar options that a user can access (i.e. for Menu Option 'Edit''Tools'):

Set myCmd = CommandBars("Worksheet menu bar").Controls("Edit")
myCmd.Controls("Delete Sheet").Enabled = False

But if the user wishes to delete the sheet, they can select the specific WorkSheet 'Tab' and Right-Click to Insert/Delete/Rename the sheet etc.

How do 'hide' these options within VBA? Or is there a Menu setting that I can be set to Enabled = False?

View 6 Replies View Related

Message Box Properties

Mar 29, 2009

message box properties. i m using this

View 3 Replies View Related

GetOpenFilename Properties

Nov 16, 2008

Is it possible to disable The "Look In:" field of the GetOpenFilename dialogue?
What I would like to do is to keep users from selecting folders other than the CurrentDirectory settings and if possible to keep the user from deleting,copying and pasteing to the files in the current dirrectory displayed. The code I have is:

View 4 Replies View Related







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