By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Excel VBA Check Box Properties ??

P: 1
I am building a spreadsheet which contains a number of progress checkboxes (from the Forms menu) and I am trying to write a macro that will insert today's date into the cell to the right of the cell which the checkbox is linked to

ActiveCell.Row & .Column have nothing to do with it (obviously)

How can I find out what cell the CheckBox is Linked to from inside VBA?
And then how can I insert today's date?

help will be appreciated.
May 3 '07 #1
Share this Question
Share on Google+
1 Reply


SammyB
Expert 100+
P: 807
I am building a spreadsheet which contains a number of progress checkboxes (from the Forms menu) and I am trying to write a macro that will insert today's date into the cell to the right of the cell which the checkbox is linked to

ActiveCell.Row & .Column have nothing to do with it (obviously)

How can I find out what cell the CheckBox is Linked to from inside VBA?
And then how can I insert today's date?

help will be appreciated.
Cannot be done by mere mortals :D, but ...
Assign this as the macro for your CheckBox. Note it assumes Check Box 1. If you set a breakpoint on the first line and a Watch on ActiveSheet.Shapes, then you can see all of the shapes on your worksheet and pick the correct one.
Expand|Select|Wrap|Line Numbers
  1. Sub CheckBox1_Click()
  2.     Dim cb As CheckBox, c As Range
  3.     Set cb = ActiveSheet.Shapes("Check Box 1").OLEFormat.Object
  4.     Set c = ActiveSheet.Range(cb.LinkedCell)
  5.     If c Then
  6.         c.Next = "On " & FormatDateTime(Now(), vbShortDate)
  7.     Else
  8.         c.Next = "Off " & FormatDateTime(Now(), vbShortDate)
  9.     End If
  10. End Sub
Note to others -- David is using a Forms CheckBox. This is not the code for any other type of checkbox.
May 3 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.