Input box

[DougRobertson]

At present, I use Input Boxes with the usual 'Yes', 'No' and 'Cancel' buttons. They return either vbYes, vbNo, or vbCancel. Simple.

However, I'm trying to create a macro that will save the open workbook in one of two folder options - 'Folder A' or 'Folder B'. I'd like to use an Input Box similar to the 'Yes No Cancel' one, but with the 'Yes' button essentially being replaced with a 'Folder A' button, the 'No' button replaced with a 'Folder B' button, and the 'Cancel' button staying as is.

Is there an easy way to do this? And what would be returned for the clicking of the various buttons, as opposed to vbYes etc.?

Or do I need to start from scratch with a new UserForm?

Thanks,

~ Doug

 

[Sahak]

Hi Doug,

You can use a simple UserForm with 3 buttons: “Folder A”, “Folder B” & “Cancel”.

 

[AlphaFrog]

Here's a link that allows you to customize the MsgBox buttons
Custom Message Box Buttons using Hooking in Excel VBA

I would probably just make a UserForm from scratch though.

 

[DougRobertson]

Thanks - it looks like just creating a new UserForm with 3 buttons is the way to go.

I've already done that, re-named the ControlButtons etc., but I don't know how to integrate it into the existing macro. Are there any good tutorials?

~ Doug

 

[AlphaFrog]

Here's a step-by-step guide
http://gregmaxey.mvps.org/Custom_MsgBox.htm

 

[Sahak]

Try this,

Option Explicit
Private Sub CommandButton_Cancel_Click()
    Unload Me
End Sub
Private Sub CommandButton_FolderA_Click()
    ActiveWorkbook.SaveAs Filename:="C:\Folder_A\Book1.xls"
End Sub
Private Sub CommandButton_FolderB_Click()
    ActiveWorkbook.SaveAs Filename:="C:\Folder_B\Book1.xls"
End Sub

 

[DougRobertson]

That's on the right track Sahak, but I have a few questions:

1) I'd like the macro to return to the original code after saving the file in the folder, and continue some processing. Is that possible? When it hits End Sub in the Private Sub the macro stops.

2) Do all the Private Subs go at the top of the existing macro? Or on their own macro?

~ Doug

 

[Sahak]

If you like after saving do an other thing, you need to call the macro you need to be run, for example if your macro named “Macro_Copy_Range”

Private Sub CommandButton_FolderA_Click()
      ActiveWorkbook.SaveAs Filename:="C:\Folder_A\Book1.xls"
      Call Macro_Copy_Range
End Sub

 

[AlphaFrog]

Put this within your Macro where UserForm1 is the name of your custom button UserForm

    [COLOR="Red"]UserForm1[/COLOR].Show
    Select Case [COLOR="Red"]UserForm1[/COLOR].Tag
        Case 0
            'Cancel was clicked
            'Cancel code goes here
        Case 1
            'FolderA was clicked
            ActiveWorkbook.SaveAs Filename:="C:\Folder_A\Book1.xls"
        Case 2
            'FolderB was clicked
            ActiveWorkbook.SaveAs Filename:="C:\Folder_B\Book1.xls"
    End Select

Put this in the UserForm Module

Private Sub CommandButton_Cancel_Click()
    Me.Tag = 0
    Me.Hide
End Sub
Private Sub CommandButton_FolderA_Click()
    Me.Tag = 1
    Me.Hide
End Sub
Private Sub CommandButton_FolderB_Click()
    Me.Tag = 2
    Me.Hide
End Sub

 

[DougRobertson]

Well that seems to be the ticket! It works great - thanks.

Just a couple of things to help in the future though if you don't mind:

1) Is there a way to get rid of the black border around one of the command buttons?

2) Does the Tab Index and Tab Stop in the Properties relate at all to those borders?

3) Is there a way to have the mouse pointer go to Button 1 when the UserForm comes up?


Thanks,

~ Doug