Rename Buttons For YesNoCancel VBA

[clark544]

Here is my code:

Private Sub Workbook_Open()
    Select Case MsgBox("my question?", vbYesNoCancel)
    Case Is = vbYes
       'Yes Code will sort Moistures and Owners
    Case Is = vbNo
        'NO code will sort Moistures and Fields
    Case Is = vbCancel
        ' Will Sort neither and allow for data entry
    Case Else
        Debug.Print "Whoops"
    End Select
End Sub

I want to rename the Yes, no, and cancel buttons to Pay Owner, Field Close Out, and Data entry. How can I go about doing that? Is there a simple way to to rename them ?

 

[BAlGaInTl]

Here is my code:

Private Sub Workbook_Open()
    Select Case MsgBox("my question?", vbYesNoCancel)
    Case Is = vbYes
       'Yes Code will sort Moistures and Owners
    Case Is = vbNo
        'NO code will sort Moistures and Fields
    Case Is = vbCancel
        ' Will Sort neither and allow for data entry
    Case Else
        Debug.Print "Whoops"
    End Select
End Sub

I want to rename the Yes, no, and cancel buttons to Pay Owner, Field Close Out, and Data entry. How can I go about doing that? Is there a simple way to to rename them ?

I think you will need to use your own custom userform for that, and instead of using case, just assign a different macro to each button.

 

[mole999]

Not sure you can rename them, as they are part of the build. I would expect you need to make a custom form and you can have the buttons any name you like then and any routine you want also

 

[clark544]

I know there are work arounds for it, but they looked really complicated and I didn't follow the code too well. I was wondering if there was something simple. If not, I'll just put it in the question, "Select Yes for x, No for Y and Cancel for Z"


Thanks for the pointers!

 

[BAlGaInTl]

I know there are work arounds for it, but they looked really complicated and I didn't follow the code too well. I was wondering if there was something simple. If not, I'll just put it in the question, "Select Yes for x, No for Y and Cancel for Z"


Thanks for the pointers!

I wouldn't want to confuse my users like that. It really isn't any more difficult than the code you are trying to do.

Design the form and set up the buttons that you want...

Then just use:

Private Sub Workbook_Open()
    UserFormName.Show
End Sub

From your userform design... double click each button, and it will take you to the code for that button. Replace whatever code that you would have used for the cases there.

If you want the form to disappear after they have clicked a button, use

Unload.Me

at the end of each button macro.

 

[clark544]

I can handle that, Thanks!

 

[BAlGaInTl]

Here is the code that you have above for a userform:

Private Sub CommandButton1_Click()
    'Yes Code will sort Moistures and Owners
    Unload.Me
End Sub
Private Sub CommandButton2_Click()
    'NO code will sort Moistures and Fields
    Unload.Me
End Sub
Private Sub CommandButton3_Click()
    ' Will Sort neither and allow for data entry
    Unload.Me
End Sub

 

[clark544]

How do I 'run" a userform? I have a userform1 and I want it to open every time the workbook opens up.

 

[mole999]

Do you mean this bit which was included earlier

Private Sub Workbook_Open()
UserFormName.Show
End Sub

 

[Greg Truby]

Tom posted a way to do this several years back. I might have it among my favorites. BRB...

<SUP>edit</SUP> Yup - found it. Tom answered this about five years ago.

http://www.mrexcel.com/forum/showthread.php?t=70581 <SUB>/edit</SUB>
<SUB></SUB>
<SUB></SUB>
<SUP></SUP>
<SUP>edit2</SUP> It's worth noting that both Tom and Ivan indicated that at the end of the day, they would probably opt for using a UserForm, as has already been suggested here.<SUB> /edit2</SUB>