Userform Question

[smpatty08]

I would like to create and Userform to prompt the User to enter the number of rows needed when the Workbook is opened. I am entering data into an existing spreadsheet for work and I currently have a Macro set up to insert a row per click of a button, but I have recently figured out that a Userform can enter the total number of rows the User needs. The rows are formatted in a certain way. Let me know if you need any more information about the spreadsheet I will be entering data into.

 

[alansidman]

Here is a macro that you can run that will insert rows below the active cell.

Option Explicit


Sub InsRows()
Dim Ins As Long
Ins = InputBox("How many rows to insert?")
Dim c As Range
Set c = ActiveCell


c.EntireRow.Resize(Ins).Insert




End Sub

 

[smpatty08]

Here is a macro that you can run that will insert rows below the active cell.

Option Explicit


Sub InsRows()
Dim Ins As Long
Ins = InputBox("How many rows to insert?")
Dim c As Range
Set c = ActiveCell


c.EntireRow.Resize(Ins).Insert




End Sub

Thank You!

I ended up taking out the input box and using the textbox from my Userform and it works like a charm. The formatting should be easy since I can write a code to find that resized range and copy formatting from a hidden row.

My current code looks like this with your code pasted in.
Option Explicit
Sub CloseButton_Click()
Unload Me
End Sub


Sub InsertRowsButton_Click()
Dim NextRow As Long
Dim c As Range
Set c = ActiveCell

' Make sure Sheet1 is active
Sheets("Sheet1").Activate


' Make sure a number is entered
If NumberofRows.Text = "" Then
MsgBox "You must enter a number."
NumberofRows.SetFocus
Exit Sub
End If


c.EntireRow.Resize(NumberofRows).Insert

' Clear the controls for the next entry
NumberofRows.Text = ""
NumberofRows.SetFocus

Unload Me

End Sub

Thanks again for your help!

 

[alansidman]

You are welcome. I am happy you have it working as needed. Since you have set c = ActiveCell, I don't think you need to activate Sheet1 as you have already determined a starting point. It is redundant.