Setting Cell Links in Forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

I am creating a spreadsheet with multiple check boxes and drop-downs, and wonder whether there is a way to globally set the forms to link to the cells which they populate - if the checkbox is in cell "G21", can I associate the value to cell G21 without manually going to the Format Control dialogue box? Doing so manually for each item is very (!) painful and time- consuming!

Many Thanks,

Alex
 
If you're very careful with the placement, you could use a macro that assigns
the linked cells:

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myDD As DropDown

For Each myCBX In ActiveSheet.CheckBoxes
With myCBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next myCBX

For Each myDD In ActiveSheet.DropDowns
With myDD
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next myDD
End Sub


the numberformat of ;;; makes it look like the cell is empty on the
worksheet--you can still see the value in the formula bar.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top