Search code examples
vbarangeinputbox

cannot enter a range in inputbox


problem

I want to make my hardcoded sheet dynamic by asking the user for input(where to start the table, how many headers etc.)

I tried asking for the user's input by using inputboxes, the title and header input work but somehow the rng inputbox doesn't work. the variable stays empty when I give it an input.

code

Option Explicit
Dim Sht As String
Dim rng As Range
Dim Start As Range
Dim Einde As Range
Dim HeaderLength As Integer
Dim SubTitel() As String
Dim Titel As String
Dim I As Integer

Dim Number As Long

'Standaard template voor de Ruimtelijst
Sub Opmaak()
    'variabelen testen
    'offset om te tellen!
    Sht = "Blad1"
    Set rng = Application.InputBox(prompt:="Waar moet de tabel beginnen?", Type:=8)
    Start = rng.Value
    Titel = InputBox(prompt:="Wat is de Titel van de tabel?")
    HeaderLength = InputBox(prompt:="Hoeveel subtitels wilt u toevoegen?")
    
    Set Einde = Range(Start).Offset(4, HeaderLength)
    ReDim SubTitel(0 To HeaderLength)
    With Worksheets(Sht)
        .Cells(2, 2).Value = Titel
        For I = 1 To HeaderLength
            SubTitel(I) = InputBox("Wat is de naam van subtitel nummer " & I & "?")
            
            'Subtitels toevoegen
            .Cells(3, I + 1).Value = SubTitel(I)
            
            .Cells(2, I + 1).Merge
        Next I

I think I assign the inputbox value wrongly for a range but I cannot find how to ask the user for a range value.


Solution

  • Try selecting the range, using the mouse cursor...

    Otherwise, you must clearly specify the range. Something like that:

    =D4:F4

    or (from other sheet):

    =SheetName!$D$4:$F$4

    In order to properly use the InputBox result, you also must change

    Dim Start As Range with Dim Start As Variant.