Please help, Subscript out of range error

JohnnyCool

New Member
Joined
Dec 31, 2016
Messages
2
Hi, I'm currently trying to write a VBA code, it looks like this:

Dim currentTopPos1 As Long
Private Sub cmdSaveData1_Click()
Dim settings As Variant
Dim info() As String
Dim index As Long

With Sheets(sheet_settings1)
ReDim info(1 To getLR1(.Name, "A") - 1)
settings = .Range("A2:D" & getLR1(.Name, "D")).Value

For index = LBound(settings, 1) To UBound(settings, 1)
With Me
info(index) = .Controls(settings(index, 1))
End With
Next index
End With

With Sheets(sheet_data1) (*Debug points here*)
.Range("A" & getLR1(.Name, "A") + 1).Resize(, UBound(info)) = info
End With
End Sub


Private Sub UserForm_Activate()
With Me
.ScrollBars = fmScrollBarsVertical
.ScrollHeight = currentTopPos1 * 1.2
End With
End Sub


Private Sub UserForm_Initialize()
Dim settings As Variant
Dim index As Long
Dim label As MSForms.label
Dim textbox As MSForms.textbox

currentTopPos = 20
With Sheets(sheet_settings1)
settings = .Range("A2:D" & getLR1(.Name, "D")).Value

For index = LBound(settings, 1) To UBound(settings, 1)
With Me
Set label = .Controls.Add("forms.label.1")
With label
.Left = UI_LEFT1
.Top = currentTopPos1
.Width = settings(index, 4)
.Height = UI_LINE_HEIGHT1
currentTopPos1 = .Top + .Height + UI_GAP1
.Caption = settings(index, 2)
End With
Set textbox = .Controls.Add("Forms.textbox.1")
With textbox
.Name = settings(index, 1)
.Left = UI_LEFT1
.Top = currentTopPos1
.Width = settings(index, 4)
.Height = settings(index, 3) * UI_LINE_HEIGHT1
If settings(index, 3) > 1 Then .MultiLine = True
currentTopPos1 = .Top + .Height + UI_GAP1
End With
.Height = 300
.Width = 380
.Caption = "Form nhap du lieu"
End With
Next index
End With
With cmdSaveData1
.Top = currentTopPos1
.Left = 310 - cmdSaveData1.Width
End With
End Sub


When I try to click the Save button, the error occurs (Subscript out of range). When I hit Debug, it points me to With Sheets(sheet_data1).

What should I do?

P/s: I followed the guide on this link: https://www.youtube.com/watch?v=arCda8sm524, I tested it myself and it worked, but when I tried to do another sheet with more columns (for the example in the link, it has 7 things in the form, But in MY case, I have 19 things), it just doesn't work.

Please help.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

JohnnyCool

New Member
Joined
Dec 31, 2016
Messages
2
Oh wait, dang it, I found my own problem.

I named my Sheet Data 1 (with space) while in the line I wrote sheet_data1 (no space)

I guess 1 space is just too much space huh :v
 

Watch MrExcel Video

Forum statistics

Threads
1,108,768
Messages
5,524,787
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top