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
ADVERTISEMENT
Jan 9, 2012
I am trying to create a workbook where the first worksheet has a list of Checkboxes and each checkbox 'unhides' or 'hides' subsequent worksheets within the workbook, depending on whether the box is checked. I can make the checkbox, I can record the macro.
Sub Test_checkbox()
' Test_checkbox Macro
Sheets("Dist").Visible = True
End Sub
How do I link them together? Ideally, I'd like to have my boss be able to 'play' with which sheets are visible (check, uncheck, check, uncheck, etc) .
View 4 Replies
View Related
Sep 5, 2013
I've done a check box on excel (ActiveX) which then enters True or False when I tick or untick it. Is there any way which you can change the True/False words to something else?
For example, if the box was ticked it would enter "Electric" instead and "N/A" if the box was unticked?
View 2 Replies
View Related
Jan 2, 2013
I'm trying ActiveX controls for the first time.
I need my code to start at the cell that the checkbox is linked to, but I don't know how to do that.
I have several checkboxes on the same sheet. I already have the checkboxes linked to cells, I just need to know how to tell the vba to find the right place to start.
View 3 Replies
View Related
Jul 30, 2014
I have a sheet in excel 2013
In column "Y" I want to add a checkBoxs activeX from cell 6 until cell 500
I don't want to repeat it 500 times )":
This is my code for a single checkBox
[Code] ...........
View 9 Replies
View Related
Nov 30, 2012
I'm using Excel 2007.
I'm building a spreadsheet which includes a few columns which have activex check boxes which are linked to cells. I want to allow the users of the spreadsheet to insert new rows if necessary. Ideally, when these new rows are inserted, the check boxes would also automatically appear in the corresponding columns of the new row (and be linked to the relevant cells), just the same way that pull-down menus and formulas automatically copy into the new row. Is there a way to do this?
View 1 Replies
View Related
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
Jun 20, 2007
I have one sheet with about 100 activex controls (and another few 100 on the other sheets in the file), most combobox, but some checkbox as well. There are 30 of them lined up, one in each row. One at a time, one control will basically change to an icon only. If you select it in design mode, the name shows in the range name area of the toolbar, but when you select properties you get the properties for the sheet. Effectively I have an icon with nothing linked to it anymore.
For a while, it seemed to be somewhat random. But for the last few days, it is now cascading down. First combobox #6, then #7, etc. I'm now on #11 or so. Seems to happen when you save then reopen the file, not if you just keep working in the file without closing it.
The macros behind the boxes are still there and run once I recreate the missing box (normally with an Alt-D, and editing the properties; but I've also created from scratch from the toolbar, and used alt-C, alt-V) they work fine. I've run a compile, with no change. Any thoughts on finding the root cause? I thought it might be from blowing up a macro, but I've tried it without even editing a macro and it has the issue.
View 4 Replies
View Related
Mar 2, 2014
I needed to put a Checkbox on a Worksheet. The standard Checkbox options in Excel FormControl and ActiveX Control were too small. I found a work-around using the following macro attached to an ActiveX Label that was formatted as Wingdings font. The macro basically changes the character from a empty box Wingdings Chr(168) to a checked box Wingdings Chr(254) when the user clicks on the label.
Private Sub Label1_Click()
If Label1.Caption = Chr(254) Then
Label1.Caption = Chr(168)
[Code]....
But I get this error on an ErrorHandler that I have included in the macro. "An error has occurred. Error number =438. Error Description=Object doesn't support this property or method."
View 3 Replies
View Related
Sep 13, 2012
I am trying to change the font size on my combo box using VBA.
I create the combobox dynamically, so need to be able to change the font too.
This is how I create the combobox, however the .Font.Size = 10 does not work and the default font size is 11.
Code:
With ActiveSheet .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=159.75, Top:=80.25, Width:=75.75, Height:=19.5) _
.Font.Size = 10
.Name = "cmbBaseD"
.OLEObjects("cmbBaseD").ListFillRange = "DynRng"
End With
View 2 Replies
View Related
Jan 24, 2013
I am trying to change checkboxes caption name using a loop so that I don't have to change them individually. Here is a simplified example of what want to do:
1) I save the caption names in a array
2) I run through a loop to rename each checkboxes using the names saved in the array
Sub change_Checkbox_Caption
Dim MyArray(1to3) as string
Dim IndexMyArray as integer
MyArray(1) = "YES"
MyArray(2) = "No"
MyArray(3) = "Maybe"
[Code]...
View 2 Replies
View Related
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
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
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
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
Jan 30, 2013
How do you changethe font size in acheckbox in excel 2010?
View 1 Replies
View Related
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
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
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
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
Jun 15, 2014
well, the clear worked well and now I have another idea for a button for my worksheet;
I want the button to insert values from other cells. so when it is clicked, the values in cells F82-F86 are entered in cells E19 - E23.
View 5 Replies
View Related
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
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
Jan 19, 2009
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.
View 3 Replies
View Related
Nov 3, 2011
I've a userform with 5 checkboxes and 1 botton
I need that every checkbox has a number associated so when i click in botton1
those values sum
e.g.: associate value 5 to checkbox1, 3 to checkbox2 and 10 to checkbox3, 4 to checkbox4, 13 to checkbox5
If I enable checkbox1 and checkbox2 in sheet("auxiliar").range("E5")=(5+10) after click botton1.
View 1 Replies
View Related
Jul 11, 2013
If cells A1 to A10 are filled with content and from range B1 to B10, only B3 and B7 are filled with content, I want Excel makes a string without spaces, just from the rows which have been filled in column A and B.
For example:
A B
1 car
2 tree
3 money tree
4 fruit
5 ball
6 food
7 water park
8 bread
9 joke
10 boat
The string I want Excel generates should be: moneytreewaterpark
So I need all the words in one string but Excel has to exclude every value from a row if the cell in column B is not filled with content. If the string is generated, is it possible to make an hyperlink from this string and open automatically in internet browser?
View 1 Replies
View Related
Jul 1, 2014
I have an assigned value of 10 to each checkbox that is checked. If the checkbox is selected I want to automatically sum the totals in a cell on my active sheet. Checkboxes are activeX placed directly on a worksheet. I currently have a work around but considering the number of checkboxes I have, coding is going to be a nightmare as I would have to code each one separately. Right now I am summing each box on another sheet and linking the summed total back to active sheet.
[Code] ....
I would like to do all check boxes with one code and sum the total in the active sheet in cell "I7". The set of checkboxes is 1 to 26.
View 3 Replies
View Related
Nov 7, 2006
I have a user form that has 12 check boxes corresponding to the months on the year. All or none of the check boxes can be ticked at the same time. How can I get the value of the check boxs to one cell on the worksheet.
ie. Check boxes 'Jan', 'Feb', 'June' and 'Oct' are ticked. On the worksheet, cell A1 would say 'Months chosen: Jan, Feb, June, Oct'
View 3 Replies
View Related
Jul 26, 2007
I have a userform with 54+ textboxes and and a checkbox. I would like to change the value of the checkbox from true to false using the keyboard when any of the textboxes have the focus. I can do this using a 'IF keycode' routine but I have to paste the code into each textbox. Is there a way of having the code in one place only, to prevent duplication?
View 2 Replies
View Related
Feb 1, 2013
I am copying a large table of data from a report generated in Firefox and pasting it into Excel 2010. The data has several columns of html checkboxes. I need to do two things with the checkboxes and would like to do a third:
1: Count how many checkboxes are ticked in each of the columns.
2: Compare a column A of checkboxes to a column B containing numbers, and then both count and highlight any row where the checkbox is ticked but column B is a 0.
3: (optional) I would like to erase the html checkboxes and, if the box was checked, replace it with a regular x in the underlying cell.
I found some code on another forum that generates a list of values for each checkbox (vba - Obtain the value of an HTML Checkbox inserted in Excel worksheet - Stack Overflow).
Based on that, I recorded a macro to extract the html Name of a single checkbox and then set up a Vlookup for the True/False value. However, I can't figure out how to automate a vlookup for every individual checkbox and put the data in the appropriate underlying cell.
View 3 Replies
View Related
