Input box and add rows (tricky, for me at least)

[Guest]

I have a named range (a1:a10) = "Name"
I have a right click function that when used within this range a menu
appears asking to Add or Delete. (The menus are disabled if the user clicks
anywhere else on the spreadsheet).
As of now, if a user right clicks on cell A3 in chooses Add, another row is
added above the selected cell (it is blank). Now the named range "Name" is
A1:A11.

What I would like is for an input box that can accept one or more variables
(comma separated. For each item entered, a new row will be inserted and the
cell will be populated with an item. Example:

A2= Bob
A3= Sue
A4= Joe

A user right clicks A3 and chooses add. An input box will appear: "enter
name(s) separated by comma. User enters: Pete, Mary, Ryan then selects OK

The results will look like this:

A2= Bob
A3= Pete
A4= Mary
A5= Ryan
A6= Sue
A7 = Joe

 

[Norman Jones]

Hi Matt,

Try something like:

'============>>
Sub Tester04()
Dim Res As Variant
Dim arr As Variant
Dim i As Long

Res = InputBox(prompt:= _
"For each Text entry, separated by " & _
"a comma, a new row will be inserted. " & _
vbNewLine & _
"The input text value will appear in the " & _
"cuurent column of the new row")

arr = Split(Res, ",")

For i = UBound(arr) To LBound(arr) Step -1
ActiveCell.EntireRow.Insert
ActiveCell.Value = arr(i)
Next i
End Sub
'<<============

Disclaimer:
Note that the above code is schematic: it is devoid of any error handling
and no consideration has been given to possible superior approaches.

 

[Norman Jones]

Hi Matt,

I failed to add, that the Split function was introduced with xl2k.

If you require equivalent code for xl97, please post back.

 

[Guest]

Norman:

Thanks. It works well.

Matt

Hi Matt,

Try something like:

'============>>
Sub Tester04()
Dim Res As Variant
Dim arr As Variant
Dim i As Long

Res = InputBox(prompt:= _
"For each Text entry, separated by " & _
"a comma, a new row will be inserted. " & _
vbNewLine & _
"The input text value will appear in the " & _
"cuurent column of the new row")

arr = Split(Res, ",")

For i = UBound(arr) To LBound(arr) Step -1
ActiveCell.EntireRow.Insert
ActiveCell.Value = arr(i)
Next i
End Sub
'<<============

Disclaimer:
Note that the above code is schematic: it is devoid of any error handling
and no consideration has been given to possible superior approaches.

 

[Tom Ogilvy]

Just for interest, here is a slightly different approach:

Sub Tester04()
Dim Res As Variant
Dim arr As Variant
Dim i As Long

Res = InputBox(prompt:= _
"For each Text entry, separated by " & _
"a comma, a new row will be inserted. " & _
vbNewLine & _
"The input text value will appear in the " & _
"cuurent column of the new row")

num = Len(Res) - Len(Application _
.Substitute(Res, ",", "")) + 1

ActiveCell.Resize(num, 1).EntireRow.Insert
ActiveCell.Value = Res
If num > 1 Then
With ActiveCell.Resize(num, 1)
.Justify
.Replace ",", ""
End With
End If

End Sub

It should work with xl97 as well. (only actually tested in xl97)

 

[Guest]

Hopefully you get this: I was testing and when I selected more the one cell
and tried to run the function, it ran into some errors. Any lines I can add
to make sure only one cell is selected?

Matt

 

[Norman Jones]

Hi Matt,

Hopefully you get this: I was testing and when I selected more the one
cell
and tried to run the function, it ran into some errors. Any lines I can
add
to make sure only one cell is selected?

Since my code operates on the active cell and only one cell cn be active,
irrespective of how many cells are included in a selection, I would not
anticipate the problems you outline.

Perhaps you could indicate how you invoked the code and what the specific
reported errors were.

That said, did you check out Tom Ogilvy's elegant alternative suggestion?

 

[Guest]

Norman: I believe I was able to invoke the error message because I defined
the range to include more than one cell. I resolve it by simply adding (I
had previously defined iSect)
If iSect.Rows.Count <> 1 Then
MsgBox "Please select only one cell."