Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to locate Cell in which control resides
Hello,
I need help getting VBA to recognize the cell in which a checkbox resides. Is there a way to know the cell position based on the location of the control. The code I am using is: With ActiveCell.Interior If CheckBox1.Value = True Then .Color = RGB(0, 255, 0) Else .Color = RGB(255, 0, 0) End If End With I basically need to change the color of the cell based on whether the control is checked or not. Thanks very much in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to locate Cell in which control resides
Hi Jamal,
Try something like: '============= Private Sub CheckBox1_Click() Dim Rng As Range Set Rng = Me.CheckBox1.TopLeftCell With Rng.Interior If Me.CheckBox1.Value = True Then .ColorIndex = 4 Else .ColorIndex = 3 End If End With End Sub '<<============= --- Regards, Norman "Jamal" wrote in message ups.com... Hello, I need help getting VBA to recognize the cell in which a checkbox resides. Is there a way to know the cell position based on the location of the control. The code I am using is: With ActiveCell.Interior If CheckBox1.Value = True Then .Color = RGB(0, 255, 0) Else .Color = RGB(255, 0, 0) End If End With I basically need to change the color of the cell based on whether the control is checked or not. Thanks very much in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to locate Cell in which control resides
To improve on my suggestion a little: if you name your checkboxes using the
cell address or row number which corresponds to their *intended* position (since they occasionally aren't exactly where they were initially placed) then you can extract that info from the name and use it directly. Eg: "0002_checkbox" for row 2 dim rownum as long rownum=clng(left(application.caller,4)) Tim "Tim Williams" <timjwilliams at gmail dot com wrote in message ... Forms checkbox or Control toolbox checkbox ? If you use forms checkboxes you can assign the same macro to all and then use Application.Caller within the macro to get a reference to the checkbox itself Sub CheckBoxes_Click() MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell .Address() End Sub Tim "Jamal" wrote in message ups.com... Norman, Thanks very much for the help. The method you described works fine. However, the spreadsheet I am trying to attach the code to has hundreds of check boxes. It would great if there is a way to assign the same macro to each of the checkboxes but still be able to learn the location of the cell where the check box resides. Is there a way to know the cell location without associating the checkbox with a specific cell? Can Excel determine the location of the box when it is checked or unchecked? Your method works for one checkbox since the code associated with the checkbox has to know what the name of the checkbox is: Set Rng = Me.CheckBox1.TopLeftCell What I would like to do is associate each checkbox with the same macro. The macro would have the capability to find the cell location of the check box as it is checked and unchecked. If the macro tells what the cell location is, then I change its color. Thanks in advance. I need help getting VBA to recognize the cell in which a checkbox resides. Is there a way to know the cell position based on the location of the control. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to locate Cell in which control resides
Sub CheckBoxes_Click() MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell .Address() End Sub Hi Tim, This works great. I get a string like $H$8. This is what I was looking for. I should be able to convert to a cell range and change the color of that cell location. I am somewhat new to Excel programming and do not know what the range format is. If I can convert the string that Application.Caller provides, I feel like I can complete this task. If you have the time to help with this, that would be great... If not, no worries. I should be able to research and make this work. Thanks a million. Jamal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify the row in which a MAX number in a column resides | Excel Worksheet Functions | |||
VLOOKUP when a range resides in Column A | Excel Discussion (Misc queries) | |||
Determining Row in Which Labeled Cell Resides | Excel Programming | |||
VBA - Open Folder Where .xls Resides | Excel Programming | |||
Locate max value cell | Excel Programming |