Custom Button Controls

  • I searched around here for custom button and found a lot of references to toolbar stuff. Unfortunately, I can't seem to add those on the Mac version of Excel so, to keep for maximum compatiblity, I was going to create my own custom buttons.

    Here's what I have: I have a great graphic that I want to use. When I use a button control and just put the picture in it, there is always a bit of a border round that MS is apparently handling - IOW, the picture does not fill the entire button area.

    So I thought I'd try the other options; label, image or textbox. They all would work somewhat and the textbox actually seems to work the best. Part of the problem with the textbox though is that it's hard to make it act like a button as disabling it so you can't click in it caused the text to be greyed out and leaving it enabled forces the cursor to an i-beam and clicking is hard to handle.

    Labels don't really work too well at all and images can't have text directly associated with it.

    So the simple question is, has anyone bothered to do this and if so, how did you manage to make it look and act like a real button (i.e. up/down images, handle clicking and mousemove events).

    Also, even if noone has any other ideas, I'll kludge something together, but, since I will have 5-10 of these on just 1 of my forms, is there a simple way of writing one "handler" for all of the "buttons"?


  • Re: Custom Button Controls

    Have you tried just making the entire graphic complete with text within a graphic editting program (perhaps you have, I'm not sure).
    I've made similar "specialty" graphics, so our multi-users can click on them and the macros function just fine.
    Insert/Picture/From file, Right Click/ Assign macro.
    After that, lock objects, so the button/icon/graphic's position and appearance won't be affected by clicking on it.
    I hope this is what you were referring to.

  • Re: Custom Button Controls

    I have done that before in other applications and did think about it for this. The reason I decided not to is because I'd have to make a custom graphic for every button and if I decided to change it later, I'd have to do it again :(

    Thanks for the thought/suggestion though!!

  • Re: Custom Button Controls

    Well, I've managed to get my labels working, at least looking, the way that I want. The background picture fits nicely and I am able to directly affect the text displayed using the properties of the label control - woo hoo.

    What I've done now to try to make it look and act like a button is to code the mousedown and mouseup events to change the effect of the label from sunken to flat. This actually looks pretty good. I also change the text color (or colour for our European friends) at the same time for additional feedback to the user.

    Other than having to write the same code for all 10 buttons (just on one form!), I'm progressing. Now I need to make it act like a button does in the event you click on the button and move the mouse outside of the button range. Normally, the button will act as if you released the mouse.

    The question is, how do I detect when the mouse leaves the confines of the control - the label acting as a button in this case? I can use the mousemove but that only works while the mouse is moving inside of the control. It doesn't tell me that it has left the control which is what I need to know.

    Thanks for any input from you guys/gals!

  • Re: Custom Button Controls


    The normal way to detect the mouse having left a control is to place another slightly larger control underneath and use it's mouse events to determine the mouse has left the other control. This approach is not perfect as a quick flick of the mouse and both controls will not have any events triggered.

    Have you, can you even in the mac version, use autoshapes. I normally use the Button autoshape.

  • Re: Custom Button Controls

    I've never used autoshape - nor heard of it. Is that with normal buttons or toolbar buttons? I know of a shape for the toolbar - I think.

    I like your idea and had actually thought of that. But, instead of having another control under the button, I was going to use the frame/form mousemove event to check the buttons. I still would need to have the mousemove handler in the button as well. I was just hoping there was a simpler way :)

    Thanks for the suggestions!!

  • Re: Custom Button Controls

    That explains it, I don't have a drawing toolbar and it's not there to add. Oh well.

    I agree, we need a MouseExit event for controls. Is there maybe a LostFocus or something????? Hmmmm, guess I'll have to search around.

    Thanks again!!!

  • Re: Custom Button Controls

    Duhhhhhh - never mind. I just realized you are talking about the autoshapes on the Excel/Sheets side. I'm doing this in VBA.

    Sorry for all the confusion. You can feel free to shoot me any time you like

    :rambo: :?

  • Re: Custom Button Controls

    Hey Andy. Yes the autoshapes and all are in both versions. However, autoshapes can only be created on worksheets. What I'm doing basically uses the worksheets for calculations and storage. The user never sees them. That's why I can't use them - they aren't available (from what I can find) to add to a VBA/Excel User Form.

    Also, I'm using both versions, Mac/PC, to write and test this as I go. The biggest issue, as always, is getting the screens to look good on both. I wish someone would standardize on fonts one day and actually have them look the same on both platforms.

    Anyway, again, thanks for the suggestions!!!! I think for now, I'll get everything else that I can done and deal with the MouseExit at some point later.

    Hey, I just had a thought ....... I don't know how to write my own classes (is that what they are called??) but I've heard them mentioned. Would there be a way of writing a MouseExit routine?

  • Re: Custom Button Controls

    Ah, didn't pickup on the fact you were using a userform.

    How about using the userform itself to trigger the mouse leaving.

    Can you post a pc version of what you have. You might be able to use a class to handle multiple occurances of the functionality.

    Does the Mac support raising of events? If vba is the same as xl97 then the answer is probably no.

  • Re: Custom Button Controls

    Hey Andy. Thanks again for the help. Unfortunately things get moved down the list pretty quick so I didn't notice your reply until I searched for all of my posts.

    Anyway, I was searching for it to post this:

    The down side is you still need the standard control handlers:

    Private Sub lbl_HomeButton_Analyzer_Click()
    End Sub
    Private Sub lbl_HomeButton_Analyzer_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
      Call CustomButton("lbl_HomeButton_Analyzer", "Down")
    End Sub
    Private Sub lbl_HomeButton_Analyzer_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
      Call CustomButton("lbl_HomeButton_Analyzer", "Up")
    End Sub

    But, it does work. I thought I'd post it here in case it helps anyone else. As soon as I get something working to handle the mouseWithin/mouseLeave, I'll post that in here as well. Just a word of warning, it may be a while as I want to get the rest of the program running before I worry about that.

    I've also included a small screen print of what it looks like. Sorry I couldn't show more of the screen but file size limitations and all.....

  • Re: Custom Button Controls

    Screen print looks good from what I can see. have you tried zipping an example to attach, I am sure Andy will come up with something.

    By the way you can get email alerts if you receive an answer to a post. Just check it in your profile

  • Re: Custom Button Controls


    I think it's enough to get the idea though rather than zipping it and yes I have the e-mail notification turned on. Just don't seem to get them all sometimes

    If a guy has asked to see Your work to get more help for You this is hardly the way to go is it?
    All they want is 100% information to help with the question You have originally asked for help with, come on buddy - play the game so to speak. Rememer as You think You have given enough informations others do not that why the request has been made, just to assist You no one else.


    BTW if the auto-notification via email fails please let me know i will be happy to look in to that for You


  • Re: Custom Button Controls

    Hey Jiuk, I have no problem with posting everything needed and such. But, what I was doing was just closing the thread out showing what my sollution was. And, since this was just about the custom buttons that I was making, I figured it was just as easy to crop the picture rather than wasting time trying to get it small enough to upload.

    Thanks for the reminder though and I will, as usual, keep that in mind when I ask for and offer help. As for the e-mail, it's probably on my side so I wouldn't worry about it. If I do notice a complete lack of them, I will let you know. As it is now though, I just don't seem to receive all of them.

    On a completely unrelated subject: What would be the odds of getting some John Smith's sent my way :) If not, have a pint for me! - cheers

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!