Macro with input box.

[Dazzawm]

I need a macro please that when I select a cell or range of cells and run the code an input box pops up and does the following.

If I enter a number and press enter it places a word or sentence into the cell(s).

1. Test
2. No data available
3. Test 3

And so on up to about 10. I can change in the code the words or sentences to suit.

Thanks.

 

[James006]

Hi,
To use an Input Box in your macro :

VBA InputBox - Get Input from a User in a Macro - VBA Code Examples

In this ArticleThe VBA Input Box with a VariableThe String Variable with an Input BoxThe Number Variable with an Input BoxGet Input from a UserReturning the input to an Excel SheetUsing VBA InputBox in Access VBAVBA Coding Made Easy This tutorial will demonstrate how to get input from a user...

www.automateexcel.com

 

[Dazzawm]

Hi,
To use an Input Box in your macro :

VBA InputBox - Get Input from a User in a Macro - VBA Code Examples

In this ArticleThe VBA Input Box with a VariableThe String Variable with an Input BoxThe Number Variable with an Input BoxGet Input from a UserReturning the input to an Excel SheetUsing VBA InputBox in Access VBAVBA Coding Made Easy This tutorial will demonstrate how to get input from a user...

www.automateexcel.com

Thanks but that doesn't really mean anything to me!

 

[James006]

Hopefully, you do no expect to be spoon-fed ...

Do you already have the initial premises of your future macro ?

 

[Dazzawm]

Hopefully, you do no expect to be spoon-fed ...

Do you already have the initial premises of your future macro ?

No that's why I posted the question.

 

[Skyybot]

Is this what you're looking for?

Option Base 1
Sub Dazz()
Dim rng As Range, cell As Range
Dim mySelectionNum As Integer, mySelectionText As String, myList()
myList = Array("No Data Available", "Test1", "Test2", "Test3", "Test4", "Test5", "Test6" _
    , "Test7", "Test8", "Test9", "Test10")
mySelectionNum = Application.InputBox("Please enter number.", "Selection Number", Type:=1)
mySelectionText = myList(mySelectionNum)
Set cell = Application.InputBox("Select a cell(s) for placement", "Cell Selection", _
    Type:=8)
cell.Value = mySelectionText
End Sub

 

[rlv01]

Sub Macro_With_Inputbox()
    Dim R As Range, rng As Range
    Dim I As Long
    Dim Msg As String
    Dim Ans As Variant

    Msg = "1. Test" & vbCr
    Msg = Msg & "2. No data available" & vbCr
    Msg = Msg & "3. Test 3"

    Ans = InputBox(Msg, "Make Selection")
    If Ans = "" Then Exit Sub                         'cancel

    If IsNumeric(Ans) Then
        I = Val(Ans)
        Select Case I
        Case 1
            Msg = "Test"
        Case 2
            Msg = "No data available"
        Case 3
            Msg = "Test 3"
'        Case 4
'            Msg = "Test 4"
'        Case 5
'            Msg = "Test 5"
'        Case 6
'            Msg = "Test 6"
'        Case 7
'            Msg = "Test 7"
'        Case 8
'            Msg = "Test 8"
'        Case 9
'            Msg = "Test 9"
'        Case 10
'            Msg = "Test 10"
        Case Else
            MsgBox "Invalid selection"
            Exit Sub
        End Select

        Set rng = Selection
        For Each R In rng
            R.Value = Msg
        Next R
    Else
        MsgBox "Invalid selection"
    End If
End Sub

 

[Dazzawm]

Both work great, much obliged squires.