How to limit excel rows when you open it

Question

I want to know if there is a way to ask someone the number of rows needed for the project and then limit the excel to it.

Example: I open the excel file, a prompt appears asking me how many rows I want, I type 10, it proceeds to add the columns with the headers rows, so there is a table with the number of rows typed and the columns that where there when you opened the file.

I know you can limit the rows after you open the Excel file, but I don't know if you can do this.

Thanks

Answer 1

I would suggest to use Application.InputBox instead of regular InputBox (or more accurately - Interactive.InputBox) because Application.InputBox allows validation of the input with its Type parameter. In your case the Type parameter must be 1 as it checks for number. The other thing you need to check is the return type, because Application.InputBox returns Variant. If user presses "Cancel", you get Boolean value, so you need to check for the type and not rely on just returned value:

Sub CreateTable()

    Dim vRowsCount As Variant
    
    vRowsCount = Application.InputBox("How many rows?", Type:=1)
    
    '// If user cancels, we get Boolean return type
    If TypeName(vRowsCount) = "Boolean" Then Exit Sub
    
    '// If we're here, we can safely proceed with table creation
    MsgBox "Number of rows: " & vRowsCount
    MsgBox TypeName(vRowsCount) '//Double

End Sub