Input Boxes

[pells]

I currently have an input box that when the user enters in a sheet name in the workbook, a specified range in the active sheet is copied to the sheet name entered in the input box. My code is as follows:

sname = InputBox("Name of sheet to copy to:", "Enter Sheet Name", dflt)

This works fine, but I need to add some radio buttons/check boxes that has a couple of options that the user of the workbook can select and depending on what is chosen depends what is copied ie. the comments in the active sheet.

Does anyone know how to create such input boxes that is able to do this?

Many thanks for taking the time to read my post and all suggestions will be welcome.

 

[tlowry]

Here's an example with three option buttons (2,3, and 4)

Option Explicit
Sub demo()
Dim sName As String
sName = InputBox("Name of sheet to copy to:", "Enter Sheet Name", "Me")
    Select Case sName
        Case ""
            MsgBox "Name is required"
        Case Else
            If ActiveSheet.Shapes("Option Button 2").ControlFormat.Value = 1 Then _
                DoWith "Option Button 2", sName
            If ActiveSheet.Shapes("Option Button 3").ControlFormat.Value = 1 Then _
                DoWith "Option Button 3", sName
            If ActiveSheet.Shapes("Option Button 4").ControlFormat.Value = 1 Then _
                DoWith "Option Button 4", sName
    End Select
End Sub
Sub DoWith(sOption, sName)
    MsgBox "Option: " & sOption & " was chosen for: " & sName
End Sub

 

[BrianExcel]

Instead of an input box why not create a simple, small user form with all your fields on it? All you checkboxes, radio buttons, text boxes, etc., can then all be in one spot and programmed to open whenever the workbook is opened or a button is pushed?

 

[pells]

Instead of an input box why not create a simple, small user form with all your fields on it? All you checkboxes, radio buttons, text boxes, etc., can then all be in one spot and programmed to open whenever the workbook is opened or a button is pushed?

Many thanks BrianExcel. Ive not worked with userforms before and dont know how to programme one so that it can do what I need it to do.

I think I can create one but its the programming that I am unsure of. Are you able to assist me here? I am after one text box and two tick boxes. When a sheet name from the workbook is entered in the text box, I copy a range from the active sheet to the sheet I have entered in the text box. The two tick boxes have options to copy the Excel comments in the range too, so the user has the options to do this or not.

Once again, many thanks for your suggestion.

 

[pells]

Here's an example with three option buttons (2,3, and 4)

Option Explicit
Sub demo()
Dim sName As String
sName = InputBox("Name of sheet to copy to:", "Enter Sheet Name", "Me")
    Select Case sName
        Case ""
            MsgBox "Name is required"
        Case Else
            If ActiveSheet.Shapes("Option Button 2").ControlFormat.Value = 1 Then _
                DoWith "Option Button 2", sName
            If ActiveSheet.Shapes("Option Button 3").ControlFormat.Value = 1 Then _
                DoWith "Option Button 3", sName
            If ActiveSheet.Shapes("Option Button 4").ControlFormat.Value = 1 Then _
                DoWith "Option Button 4", sName
    End Select
End Sub
Sub DoWith(sOption, sName)
    MsgBox "Option: " & sOption & " was chosen for: " & sName
End Sub

Many thanks tlowry.

Is it possible to change the code so that when for exampe button 1 is pressed it runs a macro and the same for button 2 and 3 etc?

Once again, many thanks.

 

[tlowry]

Many thanks tlowry.

Is it possible to change the code so that when for exampe button 1 is pressed it runs a macro and the same for button 2 and 3 etc?

Once again, many thanks.

It is possible, but I don't recommend it. The user may have to select/deselect options before taking action. Thus, the command button is a better way to go.

To assign a macro to an option button, right click the option button and choose "Assign Macro"