Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sub Extensions (Excel 2000 sp3)

    Oh, I do hate to show how dumb I am but can anyone give me a quick idea of what the "_Click", "_Change" endings mean on Excel macro sub names? (e.g. Sub "DropDown12_Change") Is this editorial or does it actual affect the timing of the execution of the macro?
    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    Don't be apologetic, Bumbler, you're doing most of the rest of us a favour!

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    The _Click says that the VBA procedure will be executed when the associated object is clicked on. The _Change procedure will execute when the associated object is changed (in the case of a drop down list, when the selection in the box changes).

    Just adding that extension to the code does not make the association between the object event and the routine. The routine must also be located in the appropriate event module behind the object.
    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    Controls such as text boxes and combo boxes (dropdown lists) have several events. Each occurs in reaction to a specific action of the user. If you write a so-called event handler, you can interact with this event.

    For example, if you type something in a text box, the Change event of the text box occurs. If you write a procedure with the name TextBoxName_Change, you can react to the change.

    If you press the tab key to go to the next control, or click somewhere else, the Before Update event will occur, only if you modified the contents of the text box. The event procedure for this event has a Cancel argument; its default value is False but you can set it to True to prevent the modification from being permanent. You can use this to check the contents of the text box:

    Private Sub MyTextBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(MyTextBox) > 10 Then
    MsgBox "The text is too long!"
    Cancel = True
    End If
    End Sub

    After the value of the text box has been modified, the AfterUpdate event occurs. You can use this to modify the value of other controls:

    Private Sub MyTextBox_AfterUpdate()
    If MyTextBox = "" Then
    ACheckBox = False
    Else
    ACheckBox = True
    End If
    End Sub

    Note that the After Update event procedure has no Cancel argument - the value of the text box has already changed, you can't prevent it from changing anymore.

    As you can see in the above examples, the name of the event procedures consists of the name of the control, followed by the name of the event (without spaces), separated by an underscore _.
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    Thanks for the reply. By "event module behind the object" are you referring to the tree on the left in the VB Editor? I see the project, under which is "Excel Objects" and "Modules". The objects listed correspond to the sheet names and then one more for the workbook. The modules are simply numbered. Is there a way to create a module which implies an event or am I reading too much into your answer? Do you mean that the sub has to be in the same sheet node under the object node?
    Thanks

  6. #6
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    I just hope I can understand the responses! Thanks for the encouragement.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    This is a whole new level I've been in denial about. Let me ask a specific question first: Does this apply to control boxes (like a combo box) on a dialog or only to control boxes created from the control toolbox?
    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    My reply applies to controls placed on a worksheet from the Control Toolbox, and also to controls on a userform created in the Visual Basic Editor. For controls on a worksheet, the code will be in the module belonging to that worksheet, and for controls on a userform, the code will be in the module belonging to the userform/

    There is another kind of control you can place on a worksheet: from the Forms toolbar. These controls have just one event each; the corresponding event procedure can have any valid name and is stored in a standard module, not in the worksheet module.

  9. #9
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    Userforms! Another topic I haven't felt ready for! The fact that you said user forms are built in the VB editor tells me they are not the same as the dialogs I'm working with, built on dialog sheets. Right?
    I should have been more specific in my post. My real question is about controls on a dialog sheet. I didn't know enough to know there were so many kinds of controls and so many differences between them. Thank you for helping me stretch my mind, I need it.
    I've been reading about event handlers and noticed references to userforms but I didn't understand most of it. I'll revist those topics another time, probably when someone tells me it's a better way to do what I'm trying to do! I'll certainly take the time to look into your reply in more detail then.

    For now, is there any way to determine or control when a macro assigned to a dialog sheet combo box is executed? I think I've determined that it gets executed after the list is pulled in but prior to the linked cell being populated.
    My dialog has two combo box controls. I'd like to populate (or specify) the drop down Input range for the second combo box based on the value chosen in the first combo box. Is there a better way to do that? I built a macro that works great in debug mode. It builds the list beautifully. When I assign it to the first box it doesn't work because it uses the existing value in the linked cell, not the new one chosen. When I assign it to the second one it executes only after I choose something from the existing list, building it after I need it!
    I really appreciate your help.
    Thanks

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    If the object is a worksheet or the workbook, you can get to the event module by right clicking on the object in the project explorer and then selecting "View Code" in the pop up menu. If the object is a control on a worksheet, and the control came from the Control Toolbox, then you can right click on the control while in design mode and select "View Code" from the pop up menu. If the object is on a form, then you can right click on it while in design mode and select "View Code" from the pop up menu.
    Legare Coleman

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    Dialog sheets were used in Excel up to version 5 for Windows 3.1 or something like that. In more recent versions, userforms have taken their place, although dialog sheets are still available for compatibility reasons.

    The last time I used dialog sheets must be 10 years ago or more, so I can't offer any help with them any more, I'm afraid.

  12. #12
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    Thanks. I think I need to spend more time learning about forms.

  13. #13
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    Now I KNOW I need to learn more about forms! Thanks for the help.
    I just found a work around in case anyone else ever reads this and needs it: I can get the value corresponding to the choice made from the combo box in the macro. It is available to the code before it is written to the linked cell ( dialognm.DropDowns("Drop Down 4").Value ). I can now put all my code in the macro assigned to the first combo box, and use the value from the choice to build the input range for the second combo box.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Extensions (Excel 2000 sp3)

    Just to help you find the events:

    If you insert e.g. a combobox from the Control Toolbox in your worksheet, rightclicking the object and selecting "View code" will automatically take you to the Visual Basic Editor and it will even create the first Event for you, called "Combobox1_Change()".

    At the top of the macro editing window, you will see two dropdowns. The left one is used to select all objects that the current module has events for. The right one will list all events available to the ovbject currently selected in the left one. See below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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