Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-08-2019, 03:16 AM   #1
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 512
Thanks: 9
Thanked 123 Times in 119 Posts
essaytee is on a distinguished road
How to self refer to current control - checkbox

Hope someone can help. I've been given an Excel spreadsheet and asked to clean it up as it's not doing what it's supposed to. It's a Leave Roster spreadsheet.

What has happened is that 67 checkboxes have been added to individual cells, in the same column.



Each checkbox is named, CheckBox1, CheckBox11 etc. the default names. The checkboxes have been entered haphazardly, not one after the other, going down the column.

Within the click event of each Checkbox, the underlying cell of the Checkbox is set to either true or false, corresponding to the Checkbox value. Now the problem is, when setting the underlying cell, all references are hardcoded eg. (Range("B23").Value), but I soon found out that some of the Checkboxes and Underlying cells do not match up.

Within the click event of the Checkbox, how do I refer to the Checkbox itself. I have already figured out how to determine current row and column but just can't generically reference the Checkbox control.

Any help appreciated.
Attached Images
File Type: jpg 2019-04-08_19-54-24.jpg (33.8 KB, 65 views)

__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is offline   Reply With Quote
Old 04-08-2019, 03:34 AM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,567
Thanks: 25
Thanked 471 Times in 447 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: How to self refer to current control - checkbox

I would simulate a control array. You then have a single click event. In the code you determine which checkbox fired the event.

http://www.siddharthrout.com/index.p...ontrol-arrays/
MajP is offline   Reply With Quote
Old 04-09-2019, 02:15 PM   #3
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 512
Thanks: 9
Thanked 123 Times in 119 Posts
essaytee is on a distinguished road
Re: How to self refer to current control - checkbox

Quote:
Originally Posted by MajP View Post
I would simulate a control array. You then have a single click event. In the code you determine which checkbox fired the event.

http://www.siddharthrout.com/index.p...ontrol-arrays/
Thanks MajP, I'm looking into this now.

__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] how to refer checkbox in a tabcontrol? anto.danny Modules & VBA 5 01-10-2016 02:48 AM
How to refer to a control on a subreport? Gkirkup Reports 1 02-12-2014 08:00 PM
How to refer to control while control has focus megamef Modules & VBA 3 08-16-2013 06:00 AM
How do you refer to a control on a subform on the tabs control? loliver Modules & VBA 1 12-26-2012 04:35 PM
How to refer to a control on another form? moto485 General 4 05-08-2012 10:11 AM




All times are GMT -8. The time now is 02:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World