cells to behave like check boxes

[omega]

Dear Excel Experts:
• I am using Excel 2007
• I have two sheets -- Sheet1 and Sheet2.
• Both sheets are almost identical.
• Sheet2 cells contains mostly “=†formulas referring to Sheet1.
• Example 1: Cell F5 of Sheet2 contains the formula “=Sheet1'!F5â€
• Example 2: Cell AE554 of Sheet2 contains the formula “=Sheet1'! AE554â€

Objective:
• Considering that range “E5 to AF555†in Sheet2 are auto filled with “=â€
formulas referring to E5 to AF555 of Sheet1, I would also like to make cells
Sheet2 “E6 to AF555†as switches that behaves like “check boxesâ€.

User’s possible scenarios in Sheet2:
• If the user mouse clicks cell E6, the macro will replace the existing
formula in cell E6 with a value of “1â€.
• In the same way, if the user mouse clicks cell AF555, the macro will
replace the existing formula in cell AF555 with a value of “1â€.
• If ever the user feels undoing what he did in cell E6, all the user will
do is to click again cell E6 and the macro will “fill†cell E6 with the
original formula the cell has.
- If appropriate, since almost all the cells in Sheet2 are just filled with
“=†formulas referring to Sheet1, the macro can just copy the “untouchedâ€
formula safely hidden in E5 and “fill†it in cell E6. Thus, the formula
“=Sheet1'!E5†in cell E5 will now be “=Sheet1'!E6†in cell E6 after the copy
fill.

Restrictions:
• This “check box†behavior should only be true to cells E6 to AF555 (in
Sheet2) only.
• Value “1†is the only allowed value to be entered by the macro when the
cell is clicked.

Extra Info:
• Cell E6 to AF555 in Sheet2 is equivalent to 13,176 boxes and expanding.
• I choose “just†cells to behave like check boxes because it is quicker and
easier to expand these boxes when the data grows.

I’m still new to excel so please indicate extra details needed for beginners
like me, particularly as to where to put the codes.

Thank you very much for your time.

Omega

 

[RadarEye]

Hi Omega,

Thirst I suggest you use a named range on sheet2 for the range
E5:AF555.

If you add orws or columns to Sheet1 change the area for the named
range.

In my macro below I used the name "Dest" for the erea on ssheet2.

With your workbook active het [Alt]+[F11].
Hit [Ctrl]+[R]
Dubble click on Sheet2.

You will see:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Insert the code below
'-start of code
Dim rngInter As Range

' Check that only one cell is selected
If Target.Cells.Count = 1 Then
' Check is selected cell is part of the area E5:AF555
Set rngInter = Intersect(Range("Dest"), Target)
If Not rngInter Is Nothing Then
' Check current contents
If Target.Formula <> "1" Then
Target.Value = 1
Else
Target.Formula = "=Sheet1!" & Target.Address
End If
End If
End If
'- End of code

HTH,

Wouter

 

[omega]

RadarEye:

Thank you very much for your time. You converted 3 paragraphs of my problem
into just a few lines of "codes". Brilliant! It works well according to my
specifications and with the code's simplicity, i have learned a lot too!

Thank you very much.

Omega

 

[omega]

Thank you!

If I may add...
I protected Sheet2. However, I get Run-time error ‘1004’ when the macro gets
at line: “Target.Value = 1â€. I would really like to protect Sheet2. What line
should I insert in the macro so it could still run smoothly under protected
mode?

Dim rngInter As Range
If Target.Cells.Count = 1 Then
Set rngInter = Intersect(Range("Dest"), Target)
If Not rngInter Is Nothing Then
If Target.Formula <> "1" Then
Target.Value = 1
Else
Target.Formula = "=Sheet1!" & Target.Address
End If
End If
End If

Omega

 

[omega]

Sorry! No need to reply my additional inquiry. I got it. Although i allowed
users to edit ranges, i missed to take out the password for that particular
range. It's solved already.

RadarEye, Again.... Thank you.

Omega