PDA

View Full Version : [SOLVED] Using VBA code to change value of a checkbox in a cell



Nilesmaxim
03-02-2016, 01:52 PM
Hello. I'm kind of desperate here. I spent two days trying to research this on line with no luck, so I joined this forum in the hopes that someone can suggest a solution.

Basically, what I'm trying to do is change a value of a check box using VBA code. Simple enough, right? Ok, now for the wrinkles.

First, I'm using automation from Access to manipulate the Excel spreadsheet, however if I can find Excel VBA code to accomplish this amazing feat, (which shouldn't be this hard in the first place), I can adapt it to work in Access, once again, using automation.

Second, the checkbox control was created via vba code through automation, and placed inside a cell, so I'm not sure if the checkbox is a form control, or an Active-x control, although I suspect the checkbox is a form control.

Ok, so here's the code I used to generate the checkbox in an excel spreadsheet. Remember... this is ACCESS VBA code manipulating an EXCEL spreadsheet using automation. There's nothing wrong with this code. It works like a champ. This code isn't the issue. The reason I'm showing this code to you is to give you an idea of how the checkbox control is created.



Public Sub InsertCheckColumn(iDataSheet As Integer, sDataColumn As String, Optional sColHead As String = "")
'------------------------------------------------------------------------------------
' Purpose: Inserts a checkbox column at the desired sDataColumn reference.
' For example, if Column C lists assets, we would insert a checkbox column at C,
' This Sub is required only if a checkbox need to be inserted (for example, to
' select desired items in an associated column), otherwise this proc can be
' safely ignored in any export process.
'
' Parameters:
' iDataSheet: Index value of worksheet containing data.
' sDataColumn: Alphabetic column index of where new column is to be inserted.
' sColHead: If used, will display a header string in the first row of the inserted column.
'------------------------------------------------------------------------------------
Dim xlSheet As Excel.Worksheet
Dim Rng As Range
Dim WorkRng As Range

Set xlSheet = xlBook.Worksheets(iDataSheet)

With xlSheet
.Columns(sDataColumn).Insert
.Columns(sDataColumn).ColumnWidth = 5

If Len(sColHead) > 0 Then .Range(sDataColumn + "1").Value = sColHead

Set WorkRng = .Range(sDataColumn + "2:" + sDataColumn + Trim(CStr(xlSheet.UsedRange.Rows.Count)))

WorkRng.Validation.Delete
WorkRng.Interior.ColorIndex = 6 'Hilights the cell range to YELLOW.
xlApp.ScreenUpdating = False
For Each Rng In WorkRng
.Checkboxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height).Characters.Text = Rng.Value

Next
xlApp.ScreenUpdating = True

End With

End Sub




(Please note that although xlBook is set in this procedure, it is declared at the module level. The same can be said for xlApp, except that xlApp has been established in an earlier procedure.)

The code above will insert a coloumn at sDataColumn, and populate the range of cells in the new column with checkboxes (and as a bonus, hi-lights the new column's cells). The key construct that actually generates the checkboxes is this:



For Each Rng In WorkRng
.Checkboxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height).Characters.Text = Rng.Value

Next



I already know the cell location of the checkbox, what I don't know is the checkbox control's name within the cell, so even though I can find the cell's address, I still can't refer to the checkbox within the cell. The checkbox is generated at run time, so I don't know what the control's name is going to be at design time.

I've tried to approach the problem by itemizing all controls in any given cell to find the control's name (since there's only one control in the cell -- the checkbox), but none of the code I've found on line seems to work.

So to summarize, I'm just looking for a way to set one of these generated check box controls' values to "CHECKED" or "TRUE" via VBA code, by referring to the checkbox using not it's control name, but it's cell's address instead.

SamT
03-02-2016, 10:04 PM
The concept of a control "In a Cell" is wrong. the Controls are overlaid on the sheet using the coordinates of the cell. Other than that, they have no "physical" relationship with the cell.

The Control's Name is "Checkbox" & n, where n is the number of that Type of Control in the order added to the sheet. IOW, if you add fifty Controls to a sheet, (in some random fashion,) ten of which are CheckBoxes, the CheckBoxes will be named "CheckBox1" thru "CheckBox10."

The Sheet's Controls Collection has all the controls' indexes in the same order as they were added to the sheet, regardless of type or name. IOW, using the previous example of fifty controls, if you first added a CheckBox, then two other Types, then another CheckBox: Controls(1) would return "CheckBox1" and Controls(4), would return "CheckBox2."

IF you are lucky and these CheckBoxes are the only controls on the sheet, then Sheets(iDataSheet).Controls(Cell.Row -1) would return the Checkbox associated with that Row. (Assuming that you started adding the Controls at Row 2.)

Another option, which always works, is to use a custom Collection
Dim DatColChkBxs As Collection

For Each Rng In WorkRng
.Checkboxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height).Characters.Text = Rng.Value
DatColChkBxs.Add Controls(Controls.Count) 'The Controls Index of last Control created equals the Count of all Controls
Next

Then DatColChkBxs(Cell.Row-1) will return the desired control

Nilesmaxim
03-03-2016, 10:17 AM
I've suspected that addressing a control directly by its cell address wasn't going to work. At least now that's been confirmed.

From the behavior exhibited during my trials, it became evident that is indeed how the checkbox controls' names were being generated. (If a spreadsheet had 5 rows, and I generated three columns, then the name of the checkbox control of the first row of each column will be "check box 1"; "check box 6"; and "check box 11" respectively.)

I only have one control type on the spreadsheet -- the checkbox -- but I have generated multiple columns as well as rows containing checkboxes. I think I can come up with some kind of mathematical relationship between the column and row generated and the actual name of the checkbox, given the premise that the control name will be "check box " + n (where n is the next sequential number assigned to a new check box control), and the number of columns created. Thankfully, the number of rows is also consistent wrt all the columns in the spreadsheet. So I don't think I'll need a custom collection, but we'll see how this shakes out.

I didn't want to proceed to do that calculation until I knew for sure that was the right way to go, so now I know that it is. Thanks.