+ Reply to Thread
Results 1 to 6 of 6

Get UserForm checkbox caption and pass to another UserForm on click or mouse down

  1. #1
    Registered User
    Join Date
    11-02-2013
    Location
    Tulsa
    MS-Off Ver
    Excel 2010
    Posts
    4

    Get UserForm checkbox caption and pass to another UserForm on click or mouse down

    Hello All,

    I am somewhat new to Excel VBA and have been searching online for help. I have created a UserForm to help with data entry and database control and need some special functions.

    I currently have 2 UserForms, one for Employee info (with multipages) and the other for specific qualifications (OQ Tasks) and dates qualified. Both will update the WorkSheet.

    The one for employee info has three multipages, one which editing existing data is done. I have a frame group (OQ Tasks) on that page with 16 checkboxes for each task required for that employee. The caption for each checkbox has the Task Number assigned to it. What I want to do is, if the qualification needs to be edited or deleted, then the user will simply click on the task checkbox and another UserForm (OQDate) will open with basic employee information, the task number (from the checkbox) and the date the task qualifcation was completed (if available) or add a date for the task.

    The problem I am having now is passing the checkbox caption (which is the task #) to a label caption on the OQDate userform. I've done it for a single checkbox, but I want to do it for any checkbox that is clicked. I've already coded it (using on MouseDown) so that the checkbox value stays the same when clicked (the OQDate userform will update the status). I ran a loop I found online and it shows the last checkbox instead of the one I clicked on.

    Also, to keep track of the checkboxes, I've renamed them to ckboxEdit201, ckboxEdit202, ckboxEdit607, etc. The tasks aren't numerically contiguous.
    Each one has its own caption property set to the last 3 digits of its name. ie ckboxEdit201 caption is 201.

    Is there a way to run an if or select statement to dynamically get the checkbox caption? And would it be placed in the Sub for OnClick or MouseDown? Or would I need to make it a separate Sub and call it in an Event Handler?

    So the synopisis would be something like:
    UserForm1:Multipage #3(PageEdit):FrameOQ: CkboxEdit201 -> onMouseDown ->Get the CheckBox Caption - pass it through MsgBox confirmation to frmOQDate (other UserForm) label caption. (Do this for any CheckBox when MouseDown).

    Thanks in advance for your help!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Get UserForm checkbox caption and pass to another UserForm on click or mouse down

    Try something like this.
    Set the frmOQDate.Caption before showing the form.

    Please Login or Register  to view this content.
    Use the same syntax for the other applicable checkboxes.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    11-02-2013
    Location
    Tulsa
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Get UserForm checkbox caption and pass to another UserForm on click or mouse down

    thanks AlphaFrog. Is there a way to make a loop or while statement? There are 16 checkboxes to get captions from just for the tasks. Im trying to avoid writing the code that many times!

  4. #4
    Registered User
    Join Date
    11-02-2013
    Location
    Tulsa
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Get UserForm checkbox caption and pass to another UserForm on click or mouse down

    Here's what I have so far. It gets the information but only displays the last checkbox information. What I need is to get it for the checkbox that was clicked on.

    Please Login or Register  to view this content.
    I also have the event handler MouseDown on the checkboxes. I use MD so that it doesn't change the value when the checkbox is clicked. It's used to call another UserForm that will update the checkbox value. This works too, but I'm trying to get a MouseDown or click event if any of the 16 checkboxes are clicked, and to get the information for that particular checkbox. Right now I have to right code for each checkbox individually.

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Get UserForm checkbox caption and pass to another UserForm on click or mouse down

    Quote Originally Posted by tulsaguy71 View Post
    thanks AlphaFrog. Is there a way to make a loop or while statement? There are 16 checkboxes to get captions from just for the tasks. Im trying to avoid writing the code that many times!
    This puts your FrameOQ Checkboxes in an array and traps their events in a class module.

    Put this in the UserForm1 code module
    Please Login or Register  to view this content.

    Insert a new class module and paste this code in it.
    Name the Class Module OQCB_Events_class

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-02-2013
    Location
    Tulsa
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Get UserForm checkbox caption and pass to another UserForm on click or mouse down

    Thanks! That's what I'm looking for. I'll try it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 03-06-2013, 12:52 PM
  2. Replies: 0
    Last Post: 11-15-2012, 12:29 PM
  3. center userform caption
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-08-2009, 10:16 AM
  4. [SOLVED] Userform Caption
    By Jason Zischke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2006, 03:55 AM
  5. How to format Userform caption?
    By davidm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2005, 02:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1