Check Box and ActiveCell

[Ted Frey]

If I have a check box linked to a particular cell, how do
I make the cell that the check box is associated with the
active cell when I check it?

[Dick Kusleika]

Ted

If your check box is from the forms toolbar, then you could attach this
macro to it and remove any linked cell

Sub cbclick()

If ActiveSheet.CheckBoxes("Check Box 1").Value = 1 Then
ActiveCell.Value = True
Else
ActiveCell.Value = False
End If

End Sub

If it's from the control toolbox, you could put a similar macro in the
change event.

HTH
--
Dick Kusleika
MVP - Excel

"Ted Frey" <tf...@erdman.com> wrote in message
news:6f8401c1c551$b9f41ad0$9de62ecf@tkmsftngxs01...

[Dick Kusleika]

Ted

I got your e-mail, but I am seemingly unable to send e-mail. Try this sub,
but notice that it changes the value of the last linked cell before it
changes linked cells.

Sub cbclick()

ActiveSheet.CheckBoxes("Check Box 1").LinkedCell _
= ActiveCell.Address

End Sub

HTH
--
Dick Kusleika
MVP - Excel

"Dick Kusleika" <di...@paragonconstructioninc.com> wrote in message
news:#A9lpiVxBHA.2104@tkmsftngp02...

[Dick Kusleika]

Ted

Your email response is at the bottom of this message.

Assuming you are using checkboxes from the forms toolbar, put the linked
cell directly beneath the checkbox and change the font color to white if you
don't want to see it. Then for each checkbox you have on the worksheet,
assign this macro:

Sub MeClick()

Dim ws As Worksheet
Dim rng As Range
Dim Msg As String, Ttl As String
Dim ActualDim As Double

Set ws = ActiveSheet
Set rng = ws.Range(ws.CheckBoxes(Application.Caller).LinkedCell)
Msg = "What is actual dimension"
Ttl = "DoubleWide"

If rng.Value Then
ActualDim = Application.InputBox(Msg, Ttl)
rng.Offset(0, 2).Value = _
"The actual width of the part is " & _
ActualDim
Else
rng.Offset(0, 2).ClearContents
End If

End Sub

I had to make some assumptions about where you wanted the info to go, so you
will have to adjust some of the above to fit your situation

HTH
--
Dick Kusleika
MVP - Excel

Thanks again, but it still isn't quite right. Like I said I'm pretty green
at this but here is what I really want to do. I have a column that contains
a number of different check boxes, one per cell. When someone checks one of
the boxes, I have a message box pop up that asks for the user to enter a
value. After doing so, that value is then added to a text string, and
inserted into a cell that is offset a few columns away. When they uncheck
the box I want those values to go away. I am creating a form where the
number of rows needed isn't known, so the user may need to add a row. These
added rows need to contain new check boxes that need to do the same thing as
all the rest. I want to run the same macro for any of these check boxes,
with the values being inserted into the appropriate cell. The appropriate
cell is always offset the same distance from the corresponding check box.
The only way the I can guess to do this to somehow make the cell
"underneath", which in this case the linked cell of the checkbox, the active
cell. The following list of commands (however ugly it may seem) does the
job for one specific row when I first go and make the cell that the check
box is in active.

Sub DoubleWide()

If ActiveCell.Value Then
Prompt = "What is the actual width of this part?"
Caption = "Double Wide (Actual Dimensions)"
ActualDim = InputBox(Prompt, Caption)
Text = "The actual width of part is "
Worksheets("Engineering Listing").Range("o211").Value = Text +
ActualDim
Else: Worksheets("Engineering Listing").Range("o211").Value = ""
End If

End Sub

This seems pretty easy and I bet there is an entirely different way to do
it. I really appreciate your help to this point.

Ted

"Dick Kusleika" <di...@paragonconstructioninc.com> wrote in message

news:OIjj4PWxBHA.2560@tkmsftngp07...

[Dick Kusleika]

Ted

As my e-mail is still down (sending, not receiving) here is a macro to get
you started on your next project.

Sub AddBox()

Dim Rng As Range
Dim Msg As String
Dim Ttl As String
Dim cb As CheckBox

Msg = "Select the cell for the checkbox"
Ttl = "Cell Selection"

Set Rng = Application.InputBox(Msg, Ttl, ActiveCell.Address, , , , , 8)
Rng.EntireRow.RowHeight = 18.75
Rng.Font.ColorIndex = 2

Set cb = Rng.Parent.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
cb.LinkedCell = Rng.Address
cb.OnAction = "cbClick"

End Sub

HTH
--
Dick Kusleika
MVP - Excel

"Dick Kusleika" <di...@paragonconstructioninc.com> wrote in message

news:e#BuOifxBHA.2048@tkmsftngp03...