[Ask-VBA] input box with fixed input

[eddorena]

Hi All,

In another thread I discussed about auto insert and get support from @HaHoBe and now I tried to make the input box only can fill by fix four values (DM, DT, DS and DW) and each value was set the amount such DM is 4, DS is 4, DT is 5 and DW is 8.

I tried using If fiction, and I don't know why not working, whether any one can help me

Plan to modify by adding "line" and change "lngNrRows" with line

 Dim lngRow As Long

  Dim lngNrRows As Long
 
  Dim line As Long

 

  lngNrRows = InputBox(Prompt:="Fill number line to insert")
 
  If lngNrRows = "DW" Then line = 4
    ElseIf lngNrRows = "DS" Then line = 4
    ElseIf lngNrRows = "DT" Then line = 5
    ElseIf lngNrRows = "Dw" Then line = 8
  End If

This is the Original

Private Sub Click_Click()
'On Error Resume Next
  Dim lngRow As Long
  Dim lngNrRows As Long
 
  lngNrRows = InputBox(Prompt:="Fill number line to insert")
  lngRow = InputBox(Prompt:="Fill number line to insert")

  With ThisWorkbook.Sheets("Sheet2")
    .Cells(lngRow - 1, "A").Resize(1, 8).Copy
    .Cells(lngRow, "A").Resize(lngNrRows, 8).Insert Shift:=xlDown
  End With
  With ThisWorkbook.Sheets("Sheet1")
    .Cells(lngRow - 1, "A").Resize(1, 8).Copy
    .Cells(lngRow, "A").Resize(lngNrRows, 8).Insert Shift:=xlDown
  End With
End Sub

Thank You

 

[dmt32]

Hi,
see if this update to your first code will do what you want

    Dim Entry            As Variant, arr As Variant, m As Variant
    Dim line               As Long
   
    arr = Array("DM", "DS", "DT", "DW")
   
    Do
        Entry = InputBox(Prompt:="Fill number line To insert")
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Sub
        m = Application.Match(Entry, arr, 0)
    Loop Until Not IsError(m)
   
    line = Choose(m, 4, 4, 5, 8)

Dave

 

[eddorena]

Hi,
see if this update to your first code will do what you want

    Dim Entry            As Variant, arr As Variant, m As Variant
    Dim line               As Long
  
    arr = Array("DM", "DS", "DT", "DW")
  
    Do
        Entry = InputBox(Prompt:="Fill number line To insert")
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Sub
        m = Application.Match(Entry, arr, 0)
    Loop Until Not IsError(m)
  
    line = Choose(m, 4, 4, 5, 8)

Dave

Hi Dave @dmt32

Tested and 100% works , Thank You
I'll learn and explore in future to use array process.

I marked as solve answer.

 

[dmt32]

Hi
Glad suggestion resolves the issue & appreciate your feedback

Dave