Could you check my code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,636
Office Version
  1. 2007
Platform
  1. Windows
I have a worksheet called SKP IMMO LIST
I am using the code below.

My problem is that after making my selections on the user form i then press the CommandButton1 but a see a run time error9,subscript out of range.
This code is then shown in yellow,
Set wsSKPIMMOLIST = ThisWorkbook.Worksheets("SKPIMMOLIST")

But i dont see why ?

Code:
Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim LastRow As Long
    Dim wsSKPIMMOLIST As Worksheet
    
    Set wsSKPIMMOLIST = ThisWorkbook.Worksheets("SKPIMMOLIST")
    
    For i = 1 To 6
        With Me.Controls("TextBox" & i)
        If .Text = "" Then
        MsgBox Choose(i, "Vehicle", "Year", "Button", _
                                "Chip", "Immo", "Notes") & _
                                " Not Entered", vbCritical, "SKP IMMO LIST"
            .SetFocus
            Exit Sub
        End If
        End With
    Next i




    With wsSKPIMMOLIST
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
    End With
    
    For i = 1 To 6
        With Me.Controls("TextBox" & i)
        wsSKPIMMOLIST.Cells(LastRow, i * 2).Value = .Text
            .Text = ""
        End With
    Next i
    MsgBox "SKP IMMO LIST UPDATED", vbInformation, "SKP IMMO LIST"
    TextBox1.SetFocus
End Sub

The sheet has a space between each word so its like this SKP IMMO LIST
If i put that where its shown in yellow then it makes things even worse etc
So i closed it up thinking the space was the issue but no ??
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Are you sure that you have no leading or trailing spaces in the sheet name?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,707
Messages
5,524,424
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top