Prompt Box Macro

**hassankhan** · 07-30-2009, 10:49 PM

Need Macro

Prompt Box
1) Prompt Box: Input Name of Bank
2) Input number of times to repeat
Restart until "done" is typed in the box

Using data gathered from the macro
3) Will input data recieved from stage one, and will repeat as many times as per stage 2 into column a.

The macro needs to do this as many times as the prompt box stage is run, to fill out a list

i.e.,

1. "SCB"
2. "3"
1. "HSBC"
2 "2"
1. Done

SCB
SCB
SCB
HSBC
HSBC
thanks for the help.

**Leith Ross** · 07-31-2009, 01:46 AM

Hello hassankhan,

The attached workbook uses a VBA UserForm to validate and input the data into column "A" starting at cell "A2". Here is the UserForm code.

To run the macro press the keys Ctrl+e

Dim RepeatCount As Integer

Private Sub CommandButton1_Click()
 'DONE
 
  Dim I As Long
  Dim LastEntry As Range
  Dim Rng As Range
  Dim RngEnd As Range
  
  
    Set Rng = ActiveSheet.Range("A2")
    Set RngEnd = Cells(Rows.Count, Rng.Column).End(xlUp)
    Set LastEntry = IIf(RngEnd.Row <= Rng.Row, Rng, RngEnd.Offset(1, 0))
    
    For I = 1 To RepeatCount
      LastEntry = TextBox1
      Set LastEntry = LastEntry.Offset(1, 0)
    Next I
    
    TextBox1.SelStart = 0
    TextBox1.SelLength = Len(TextBox1)
    TextBox1.SetFocus
    
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  
  If TextBox2 = "" Then Exit Sub
  
  On Error Resume Next
    RepeatCount = CInt(TextBox2.Value)

  If Err <> 0 Or RepeatCount < 0 Then
    Cancel = True
    MsgBox "You must enter a number greater than zero."
    TextBox2.SelStart = 0
    TextBox2.SelLength = Len(TextBox2)
    TextBox2.SetFocus
  End If
  
End Sub

**hassankhan** · 08-12-2009, 08:29 AM

Just one change I need.

If I input
MCB and repeat 1 time

then
hbl and repeat 5 times

The mcb value is over written.

I need it so that the value is not replaced

**Leith Ross** · 08-12-2009, 01:43 PM

Hello hassankhan,

I changed the macro in the attached workbook. The corrected code appears below.

Private Sub CommandButton1_Click()
 'DONE
 
  Dim I As Long
  Dim LastEntry As Range
  Dim Rng As Range
  Dim RngEnd As Range
  
  
    Set Rng = ActiveSheet.Range("A2")
    Set RngEnd = Cells(Rows.Count, Rng.Column).End(xlUp)
    Set LastEntry = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd.Offset(1, 0))
    
    For I = 1 To RepeatCount
      LastEntry = TextBox1
      Set LastEntry = LastEntry.Offset(1, 0)
    Next I
    
    TextBox1.SelStart = 0
    TextBox1.SelLength = Len(TextBox1)
    TextBox1.SetFocus
    
End Sub