Input Boxes

pells

Active Member
Joined
Dec 5, 2008
Messages
361
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
Here's an example with three option buttons (2,3, and 4)

Code:
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

Well-known Member
Joined
Apr 21, 2010
Messages
969
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

Active Member
Joined
Dec 5, 2008
Messages
361
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

Active Member
Joined
Dec 5, 2008
Messages
361
Here's an example with three option buttons (2,3, and 4)

Code:
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

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,123,258
Messages
5,600,564
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top