Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
linked cell to change another cell
I have a checkbox linked to cell A1. In the cell B1 I have a number which
could be (say) 1 or 2. When the checkbox is "unchecked" and therefore A1 = FALSE I want to clear the contents of B1. I can't do it by a formula in B1 because the number has been entered manually. The best way I can think of is to run a macro when checkbox is clicked, and if A1 = FALSE then clearcontents of B1, but how do I refer to the linked cell for the particular checkbox in the macro? This same macro would be run for up to 50 checkboxes all in column "A" so I could just refer to the cell by Cell("A" & rownum).Offset(0, 1) but how do I find out what 'rownum' is for the checkbox that has been clicked please? Regards, Brett |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
linked cell to change another cell
First, I would use the checkbox from the Forms toolbar--not the checkbox from
the Control Toolbox toolbar. Each of the checkboxes from the Forms toolbar could have the same macro assigned to it. Here are two routines. The first lays out checkboxes from the forms toolbar in C3:C10. That's a one time only routine (or rerun when you need to). The second routine is the one that does the work when you click the checkboxes. Option Explicit Sub LayOutCheckboxes() Dim myCBX As CheckBox Dim myCell As Range Dim wks As Worksheet Set wks = ActiveSheet With wks .CheckBoxes.Delete 'nice for testing For Each myCell In .Range("C3:C10").Cells With myCell Set myCBX = .Parent.CheckBoxes.Add _ (Top:=.Top, Width:=.Width, _ Left:=.Left, Height:=.Height) With myCBX .LinkedCell = myCell.Offset(0, -2).Address(external:=True) .Caption = "" .Name = "CBX_" & myCell.Address(0, 0) .OnAction = "'" & ThisWorkbook.Name & "'!CbxClick" End With End With Next myCell End With End Sub Sub CBXClick() Dim myCBX As CheckBox With ActiveSheet Set myCBX = .CheckBoxes(Application.Caller) If myCBX.Value = xlOff Then .Cells(myCBX.TopLeftCell.Row, "B").ClearContents End If End With End Sub Brettjg wrote: I have a checkbox linked to cell A1. In the cell B1 I have a number which could be (say) 1 or 2. When the checkbox is "unchecked" and therefore A1 = FALSE I want to clear the contents of B1. I can't do it by a formula in B1 because the number has been entered manually. The best way I can think of is to run a macro when checkbox is clicked, and if A1 = FALSE then clearcontents of B1, but how do I refer to the linked cell for the particular checkbox in the macro? This same macro would be run for up to 50 checkboxes all in column "A" so I could just refer to the cell by Cell("A" & rownum).Offset(0, 1) but how do I find out what 'rownum' is for the checkbox that has been clicked please? Regards, Brett -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
linked cell to change another cell
Thanks alot Dave, as always (?) your code works first time.
One other thing with this is that I want to change the colour of the checkbox depending on whether it's checked or not. In the interim I have made the check boxes transparent and get the illusion of them changing colour by changing the colour of the cell behind it (which is also the linked cell) but it's not really a very elegant way to do it. The code I have is as follows and I have commented out the two lines that don't work for changing checkbox colour: Sub clear_batch_qty() Dim myCBX As CheckBox With ActiveSheet Set myCBX = .CheckBoxes(Application.Caller) If myCBX.Value = xlOff Then .Cells(myCBX.TopLeftCell.Row, "E").ClearContents .Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 40 .Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 40 ' .Shapes(myCBX).ShapeRange.Select ' Selection.Fill.ForeColor.SchemeColor = 47 Else .Cells(myCBX.TopLeftCell.Row, "E").Value = 1 .Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 10 .Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 10 End If .Cells(myCBX.TopLeftCell.Row, "E").Select End With End Sub Regards, Brett |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
linked cell to change another cell
Maybe you can merge this into your code:
With myCBX.ShapeRange With .Fill .Visible = msoTrue .Solid .ForeColor.SchemeColor = 47 .Transparency = 0# End With 'delete this if you don't want to change the line attributes With .Line .Weight = 0.75 .DashStyle = msoLineSquareDot .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 57 .BackColor.RGB = RGB(255, 255, 255) End With End With Wouldn't you want the opposite to happen if the checkbox is checked? (in the Else portion of your if/then/else statement?) Brettjg wrote: Thanks alot Dave, as always (?) your code works first time. One other thing with this is that I want to change the colour of the checkbox depending on whether it's checked or not. In the interim I have made the check boxes transparent and get the illusion of them changing colour by changing the colour of the cell behind it (which is also the linked cell) but it's not really a very elegant way to do it. The code I have is as follows and I have commented out the two lines that don't work for changing checkbox colour: Sub clear_batch_qty() Dim myCBX As CheckBox With ActiveSheet Set myCBX = .CheckBoxes(Application.Caller) If myCBX.Value = xlOff Then .Cells(myCBX.TopLeftCell.Row, "E").ClearContents .Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 40 .Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 40 ' .Shapes(myCBX).ShapeRange.Select ' Selection.Fill.ForeColor.SchemeColor = 47 Else .Cells(myCBX.TopLeftCell.Row, "E").Value = 1 .Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 10 .Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 10 End If .Cells(myCBX.TopLeftCell.Row, "E").Select End With End Sub Regards, Brett -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
linked cell to change another cell
Just what I needed, thakyou very much. My colour palette is quite different
so maybe that threw you off when you asked if I wanted the opposite. Regards. "Dave Peterson" wrote: Maybe you can merge this into your code: With myCBX.ShapeRange With .Fill .Visible = msoTrue .Solid .ForeColor.SchemeColor = 47 .Transparency = 0# End With 'delete this if you don't want to change the line attributes With .Line .Weight = 0.75 .DashStyle = msoLineSquareDot .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 57 .BackColor.RGB = RGB(255, 255, 255) End With End With Wouldn't you want the opposite to happen if the checkbox is checked? (in the Else portion of your if/then/else statement?) Brettjg wrote: Thanks alot Dave, as always (?) your code works first time. One other thing with this is that I want to change the colour of the checkbox depending on whether it's checked or not. In the interim I have made the check boxes transparent and get the illusion of them changing colour by changing the colour of the cell behind it (which is also the linked cell) but it's not really a very elegant way to do it. The code I have is as follows and I have commented out the two lines that don't work for changing checkbox colour: Sub clear_batch_qty() Dim myCBX As CheckBox With ActiveSheet Set myCBX = .CheckBoxes(Application.Caller) If myCBX.Value = xlOff Then .Cells(myCBX.TopLeftCell.Row, "E").ClearContents .Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 40 .Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 40 ' .Shapes(myCBX).ShapeRange.Select ' Selection.Fill.ForeColor.SchemeColor = 47 Else .Cells(myCBX.TopLeftCell.Row, "E").Value = 1 .Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 10 .Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 10 End If .Cells(myCBX.TopLeftCell.Row, "E").Select End With End Sub Regards, Brett -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
linked cell to change another cell
I meant that if you change the color when the checkbox is not checked, then
maybe you want to change the color back if the checkbox is checked. Much like you did with column D of that row. Brettjg wrote: Just what I needed, thakyou very much. My colour palette is quite different so maybe that threw you off when you asked if I wanted the opposite. Regards. "Dave Peterson" wrote: Maybe you can merge this into your code: With myCBX.ShapeRange With .Fill .Visible = msoTrue .Solid .ForeColor.SchemeColor = 47 .Transparency = 0# End With 'delete this if you don't want to change the line attributes With .Line .Weight = 0.75 .DashStyle = msoLineSquareDot .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 57 .BackColor.RGB = RGB(255, 255, 255) End With End With Wouldn't you want the opposite to happen if the checkbox is checked? (in the Else portion of your if/then/else statement?) Brettjg wrote: Thanks alot Dave, as always (?) your code works first time. One other thing with this is that I want to change the colour of the checkbox depending on whether it's checked or not. In the interim I have made the check boxes transparent and get the illusion of them changing colour by changing the colour of the cell behind it (which is also the linked cell) but it's not really a very elegant way to do it. The code I have is as follows and I have commented out the two lines that don't work for changing checkbox colour: Sub clear_batch_qty() Dim myCBX As CheckBox With ActiveSheet Set myCBX = .CheckBoxes(Application.Caller) If myCBX.Value = xlOff Then .Cells(myCBX.TopLeftCell.Row, "E").ClearContents .Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 40 .Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 40 ' .Shapes(myCBX).ShapeRange.Select ' Selection.Fill.ForeColor.SchemeColor = 47 Else .Cells(myCBX.TopLeftCell.Row, "E").Value = 1 .Cells(myCBX.TopLeftCell.Row, "D").Interior.ColorIndex = 10 .Cells(myCBX.TopLeftCell.Row, "D").Font.ColorIndex = 10 End If .Cells(myCBX.TopLeftCell.Row, "E").Select End With End Sub Regards, Brett -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I have a linked cell auto-size to fit the linked data? | Excel Discussion (Misc queries) | |||
Listbox linked cell does not change | Excel Worksheet Functions | |||
How to force a cell value to change if Linked cell moves? | Excel Discussion (Misc queries) | |||
how do i record changing cell data (cell is dde linked) | Excel Discussion (Misc queries) | |||
Change sheet name on linked cell by dropdown box | Excel Worksheet Functions |