| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a Variable to Reference a Check Box Object
In a larger section of code I am trying to use a variable to reference a
check box. I have listed code samples below to attempt to explain my question. Assume that I have six columns of data. In row one I have dates. In row two I have check boxes. I want to loop through the columns, comparing two sets of dates at a time. Based on that comparison, I want to hit a check box in one of the two columns below those dates. The first code section does what I want but is not efficient. Is there a way to code the change in the second section to somehow use the lColCount variable to identify the check box to be hit? Option Explicit Dim lColCount As Long Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then If lColCount = 1 Then ActiveSheet.CheckBoxes("Check box 1").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 2").Value = xlOn End If If lColCount = 3 Then ActiveSheet.CheckBoxes("Check box 3").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 4").Value = xlOn End If If lColCount = 5 Then ActiveSheet.CheckBoxes("Check box 5").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 6").Value = xlOn End If End If Next lColCount End Sub ---------------------------------------------------------------------- Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then '(Can I use lColCount to reference this check box) = xlOn Else '(Can I use lColCount to reference this check box) = xlOn End If Next lColCount End Sub -- Ken Hudson |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use a Variable to Reference a Check Box Object
Try the below
ActiveSheet.CheckBoxes("Check box " & lColCount).Value = xlOn If this post helps click Yes --------------- Jacob Skaria "Ken Hudson" wrote: In a larger section of code I am trying to use a variable to reference a check box. I have listed code samples below to attempt to explain my question. Assume that I have six columns of data. In row one I have dates. In row two I have check boxes. I want to loop through the columns, comparing two sets of dates at a time. Based on that comparison, I want to hit a check box in one of the two columns below those dates. The first code section does what I want but is not efficient. Is there a way to code the change in the second section to somehow use the lColCount variable to identify the check box to be hit? Option Explicit Dim lColCount As Long Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then If lColCount = 1 Then ActiveSheet.CheckBoxes("Check box 1").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 2").Value = xlOn End If If lColCount = 3 Then ActiveSheet.CheckBoxes("Check box 3").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 4").Value = xlOn End If If lColCount = 5 Then ActiveSheet.CheckBoxes("Check box 5").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 6").Value = xlOn End If End If Next lColCount End Sub ---------------------------------------------------------------------- Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then '(Can I use lColCount to reference this check box) = xlOn Else '(Can I use lColCount to reference this check box) = xlOn End If Next lColCount End Sub -- Ken Hudson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use a Variable to Reference a Check Box Object
Adjust the value of lColCount to suit your requirment..
If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below ActiveSheet.CheckBoxes("Check box " & lColCount).Value = xlOn If this post helps click Yes --------------- Jacob Skaria "Ken Hudson" wrote: In a larger section of code I am trying to use a variable to reference a check box. I have listed code samples below to attempt to explain my question. Assume that I have six columns of data. In row one I have dates. In row two I have check boxes. I want to loop through the columns, comparing two sets of dates at a time. Based on that comparison, I want to hit a check box in one of the two columns below those dates. The first code section does what I want but is not efficient. Is there a way to code the change in the second section to somehow use the lColCount variable to identify the check box to be hit? Option Explicit Dim lColCount As Long Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then If lColCount = 1 Then ActiveSheet.CheckBoxes("Check box 1").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 2").Value = xlOn End If If lColCount = 3 Then ActiveSheet.CheckBoxes("Check box 3").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 4").Value = xlOn End If If lColCount = 5 Then ActiveSheet.CheckBoxes("Check box 5").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 6").Value = xlOn End If End If Next lColCount End Sub ---------------------------------------------------------------------- Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then '(Can I use lColCount to reference this check box) = xlOn Else '(Can I use lColCount to reference this check box) = xlOn End If Next lColCount End Sub -- Ken Hudson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use a Variable to Reference a Check Box Object
Option Explicit
Sub PopCheckBoxes() Dim lColCount As Long 'moved inside the procedure With activesheet For lColCount = 1 To 6 Step 2 If .Cells(1, lColCount).value .Cells(1, lColCount + 1).value Then .CheckBoxes("Check box " & lcolcount).Value = xlOn .CheckBoxes("Check box " & lcolcount + 1).Value = xloff Else .CheckBoxes("Check box " & lcolcount).Value = xlOff .CheckBoxes("Check box " & lcolcount + 1).Value = xlOn End If Next lColCount End with End Sub Don't you want the "opposite" checkbox unchecked? Ken Hudson wrote: In a larger section of code I am trying to use a variable to reference a check box. I have listed code samples below to attempt to explain my question. Assume that I have six columns of data. In row one I have dates. In row two I have check boxes. I want to loop through the columns, comparing two sets of dates at a time. Based on that comparison, I want to hit a check box in one of the two columns below those dates. The first code section does what I want but is not efficient. Is there a way to code the change in the second section to somehow use the lColCount variable to identify the check box to be hit? Option Explicit Dim lColCount As Long Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then If lColCount = 1 Then ActiveSheet.CheckBoxes("Check box 1").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 2").Value = xlOn End If If lColCount = 3 Then ActiveSheet.CheckBoxes("Check box 3").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 4").Value = xlOn End If If lColCount = 5 Then ActiveSheet.CheckBoxes("Check box 5").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 6").Value = xlOn End If End If Next lColCount End Sub ---------------------------------------------------------------------- Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then '(Can I use lColCount to reference this check box) = xlOn Else '(Can I use lColCount to reference this check box) = xlOn End If Next lColCount End Sub -- Ken Hudson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use a Variable to Reference a Check Box Object
Jacob and Dave,
Thanks for the reponses. These references don't seem to work. As I think about this, I don't think this can be done anyway. These check boxes are floating objects that can be placed anywhere on the worksheet and can't be assigned to a specific cell. I think they can only be addressed by their object name, e.g. "Check box 6." Does this seem correct to you? -- Ken Hudson "Ken Hudson" wrote: In a larger section of code I am trying to use a variable to reference a check box. I have listed code samples below to attempt to explain my question. Assume that I have six columns of data. In row one I have dates. In row two I have check boxes. I want to loop through the columns, comparing two sets of dates at a time. Based on that comparison, I want to hit a check box in one of the two columns below those dates. The first code section does what I want but is not efficient. Is there a way to code the change in the second section to somehow use the lColCount variable to identify the check box to be hit? Option Explicit Dim lColCount As Long Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then If lColCount = 1 Then ActiveSheet.CheckBoxes("Check box 1").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 2").Value = xlOn End If If lColCount = 3 Then ActiveSheet.CheckBoxes("Check box 3").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 4").Value = xlOn End If If lColCount = 5 Then ActiveSheet.CheckBoxes("Check box 5").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 6").Value = xlOn End If End If Next lColCount End Sub ---------------------------------------------------------------------- Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then '(Can I use lColCount to reference this check box) = xlOn Else '(Can I use lColCount to reference this check box) = xlOn End If Next lColCount End Sub -- Ken Hudson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use a Variable to Reference a Check Box Object
That code didn't do anything based on the location of the checkboxes. It did
everything based on the name of the checkbox. But you didn't explain how the suggested code didn't work for you. Ken Hudson wrote: Jacob and Dave, Thanks for the reponses. These references don't seem to work. As I think about this, I don't think this can be done anyway. These check boxes are floating objects that can be placed anywhere on the worksheet and can't be assigned to a specific cell. I think they can only be addressed by their object name, e.g. "Check box 6." Does this seem correct to you? -- Ken Hudson "Ken Hudson" wrote: In a larger section of code I am trying to use a variable to reference a check box. I have listed code samples below to attempt to explain my question. Assume that I have six columns of data. In row one I have dates. In row two I have check boxes. I want to loop through the columns, comparing two sets of dates at a time. Based on that comparison, I want to hit a check box in one of the two columns below those dates. The first code section does what I want but is not efficient. Is there a way to code the change in the second section to somehow use the lColCount variable to identify the check box to be hit? Option Explicit Dim lColCount As Long Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then If lColCount = 1 Then ActiveSheet.CheckBoxes("Check box 1").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 2").Value = xlOn End If If lColCount = 3 Then ActiveSheet.CheckBoxes("Check box 3").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 4").Value = xlOn End If If lColCount = 5 Then ActiveSheet.CheckBoxes("Check box 5").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 6").Value = xlOn End If End If Next lColCount End Sub ---------------------------------------------------------------------- Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then '(Can I use lColCount to reference this check box) = xlOn Else '(Can I use lColCount to reference this check box) = xlOn End If Next lColCount End Sub -- Ken Hudson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use a Variable to Reference a Check Box Object
Hi Dave,
I am just now realizing what is going on. I need to name the check boxes to include the varible the matches the column number in which the check box is sitting. That is how the code will work with the column variable. The check boxes already have names asigned that I need to change. Is there a trick to renaming them? When I right click on the object and go to the Name Box in Excel to rename it, it doesn't seem to work. When I rename "Check Box 48" to "Check Box 3" and hit the return key, the curser jumps to another cell and the name doesn't change. -- Ken Hudson "Dave Peterson" wrote: That code didn't do anything based on the location of the checkboxes. It did everything based on the name of the checkbox. But you didn't explain how the suggested code didn't work for you. Ken Hudson wrote: Jacob and Dave, Thanks for the reponses. These references don't seem to work. As I think about this, I don't think this can be done anyway. These check boxes are floating objects that can be placed anywhere on the worksheet and can't be assigned to a specific cell. I think they can only be addressed by their object name, e.g. "Check box 6." Does this seem correct to you? -- Ken Hudson "Ken Hudson" wrote: In a larger section of code I am trying to use a variable to reference a check box. I have listed code samples below to attempt to explain my question. Assume that I have six columns of data. In row one I have dates. In row two I have check boxes. I want to loop through the columns, comparing two sets of dates at a time. Based on that comparison, I want to hit a check box in one of the two columns below those dates. The first code section does what I want but is not efficient. Is there a way to code the change in the second section to somehow use the lColCount variable to identify the check box to be hit? Option Explicit Dim lColCount As Long Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then If lColCount = 1 Then ActiveSheet.CheckBoxes("Check box 1").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 2").Value = xlOn End If If lColCount = 3 Then ActiveSheet.CheckBoxes("Check box 3").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 4").Value = xlOn End If If lColCount = 5 Then ActiveSheet.CheckBoxes("Check box 5").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 6").Value = xlOn End If End If Next lColCount End Sub ---------------------------------------------------------------------- Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then '(Can I use lColCount to reference this check box) = xlOn Else '(Can I use lColCount to reference this check box) = xlOn End If Next lColCount End Sub -- Ken Hudson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use a Variable to Reference a Check Box Object
I'd remove the checkboxes and re-add them.
But you should be able to rename them using the technique you described. I'd guess that you actually named a range using that string. Insert|name|define and you can see if you have any of those "oopsies" names to delete. If the checkboxes could be placed anywhere, you may find naming the checkboxes based on the cells that "control" them would be better. If you can specify the range(s) that get the checkboxes and what cells control each of them, the macro to add these checkboxes isn't too difficult to implement. Ken Hudson wrote: Hi Dave, I am just now realizing what is going on. I need to name the check boxes to include the varible the matches the column number in which the check box is sitting. That is how the code will work with the column variable. The check boxes already have names asigned that I need to change. Is there a trick to renaming them? When I right click on the object and go to the Name Box in Excel to rename it, it doesn't seem to work. When I rename "Check Box 48" to "Check Box 3" and hit the return key, the curser jumps to another cell and the name doesn't change. -- Ken Hudson "Dave Peterson" wrote: That code didn't do anything based on the location of the checkboxes. It did everything based on the name of the checkbox. But you didn't explain how the suggested code didn't work for you. Ken Hudson wrote: Jacob and Dave, Thanks for the reponses. These references don't seem to work. As I think about this, I don't think this can be done anyway. These check boxes are floating objects that can be placed anywhere on the worksheet and can't be assigned to a specific cell. I think they can only be addressed by their object name, e.g. "Check box 6." Does this seem correct to you? -- Ken Hudson "Ken Hudson" wrote: In a larger section of code I am trying to use a variable to reference a check box. I have listed code samples below to attempt to explain my question. Assume that I have six columns of data. In row one I have dates. In row two I have check boxes. I want to loop through the columns, comparing two sets of dates at a time. Based on that comparison, I want to hit a check box in one of the two columns below those dates. The first code section does what I want but is not efficient. Is there a way to code the change in the second section to somehow use the lColCount variable to identify the check box to be hit? Option Explicit Dim lColCount As Long Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then If lColCount = 1 Then ActiveSheet.CheckBoxes("Check box 1").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 2").Value = xlOn End If If lColCount = 3 Then ActiveSheet.CheckBoxes("Check box 3").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 4").Value = xlOn End If If lColCount = 5 Then ActiveSheet.CheckBoxes("Check box 5").Value = xlOn Else ActiveSheet.CheckBoxes("Check box 6").Value = xlOn End If End If Next lColCount End Sub ---------------------------------------------------------------------- Sub PopCheckBoxes() For lColCount = 1 To 6 Step 2 If Cells(1, lColCount) Cells(1, lColCount + 1) Then '(Can I use lColCount to reference this check box) = xlOn Else '(Can I use lColCount to reference this check box) = xlOn End If Next lColCount End Sub -- Ken Hudson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I pass a variable cell reference to a Range Object? | Excel Programming | |||
| Check an object reference | Excel Programming | |||
| Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Linear Mode
