Edited code now i get End With Without With error

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Evening,

I have the code below

Ive added the line of code shown in red so the year function will only be added if combobo3 was selected for HONDA
Im now getting the error message End With Without With and the text shown in blue below is highlighted in yellow when i debug.

I dont see what i need to add / fix it.

Please advise

Rich (BB code):
  If Len(Me.TextBox2.Value) = 17 Then
    Dim i As Long, x As Long
    Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
    Application.ScreenUpdating = False
    For i = 1 To 8
      ControlsArr(i) = Controls(IIf(i > 2, "ComboBox", "TextBox") & i).Value
    Next i
    
    With ThisWorkbook.Worksheets("MCLIST")
      .Range("A8").EntireRow.Insert Shift:=xlDown
      .Range("A8:K8").Borders.Weight = xlThin
      .Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
      
      If ComboBox3.Value = "HONDA" Then
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbRed
      .Cells(8, 9).Font.Color = vbRed
      Else
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbBlack
      .Cells(8, 9).Font.Color = vbBlack
      End If
      
      If OptionButton1.Value Then .Cells(8, 10).Value = "YES"
      If OptionButton2.Value Then .Cells(8, 10).Value = "NO"
      If OptionButton2.Value Then .Cells(8, 11).Value = "N/A"
      If OptionButton7.Value Then .Cells(8, 11).Value = "BUNDLE"
      If OptionButton8.Value Then .Cells(8, 11).Value = "GREY"
      If OptionButton9.Value Then .Cells(8, 11).Value = "RED"
      If OptionButton10.Value Then .Cells(8, 11).Value = "BLACK"
      If OptionButton11.Value Then .Cells(8, 11).Value = "CLEAR"
      
      If ComboBox3.Value = "HONDA" Then
      ns = Array("X", "Y", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", _
                 "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S")
      For i = 0 To UBound(ns)
        If Mid(Range("B8").Value, 10, 1) = ns(i) Then
          Range("I8").Value = "" & 2000 + i
          Exit For
        End If
      Next
      
      Application.EnableEvents = False
      If .AutoFilterMode Then .AutoFilterMode = False
      x = .Cells(.Rows.Count, 1).End(xlUp).Row
      .Range("A7:K" & x).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlGuess
      .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
      Application.Goto Selection, True
    End With
    ActiveWorkbook.Save
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Unload McListForm
  Else
    MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MCLIST TRANSFER"
    TextBox2.SetFocus
  End If
  End If
      If Me.ComboBox3.Value = "SUZUKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "YAMAHA" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "KAWASAKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    End If
End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I did enter it in a place or two but just got worse and then seen different error messages.
 
Upvote 0
Try the code below (untested)
Rich (BB code):
    If Len(Me.TextBox2.Value) = 17 Then
        Dim i As Long, x As Long
        Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
        Application.ScreenUpdating = False
        For i = 1 To 8
            ControlsArr(i) = Controls(IIf(i > 2, "ComboBox", "TextBox") & i).Value
        Next i
    
        With ThisWorkbook.Worksheets("MCLIST")
            .Range("A8").EntireRow.Insert Shift:=xlDown
            .Range("A8:K8").Borders.Weight = xlThin
            .Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
      
            If ComboBox3.Value = "HONDA" Then
                .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbRed
                .Cells(8, 9).Font.Color = vbRed
            Else
                .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbBlack
                .Cells(8, 9).Font.Color = vbBlack
            End If
      
            If OptionButton1.Value Then .Cells(8, 10).Value = "YES"
            If OptionButton2.Value Then .Cells(8, 10).Value = "NO"
            If OptionButton2.Value Then .Cells(8, 11).Value = "N/A"
            If OptionButton7.Value Then .Cells(8, 11).Value = "BUNDLE"
            If OptionButton8.Value Then .Cells(8, 11).Value = "GREY"
            If OptionButton9.Value Then .Cells(8, 11).Value = "RED"
            If OptionButton10.Value Then .Cells(8, 11).Value = "BLACK"
            If OptionButton11.Value Then .Cells(8, 11).Value = "CLEAR"
      
            If ComboBox3.Value = "HONDA" Then
                ns = Array("X", "Y", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", _
                           "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S")
                For i = 0 To UBound(ns)
                    If Mid(Range("B8").Value, 10, 1) = ns(i) Then
                        Range("I8").Value = "" & 2000 + i
                        Exit For
                    End If
                Next
            End If
        
            Application.EnableEvents = False
            If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A7:K" & x).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlGuess
            .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
            Application.Goto Selection, True
        End With
        ActiveWorkbook.Save
        MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"

        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Unload McListForm
    Else
        MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MCLIST TRANSFER"
        TextBox2.SetFocus
    End If

    If Me.ComboBox3.Value = "SUZUKI" Then
        MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "YAMAHA" Then
        MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "KAWASAKI" Then
        MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    End If
End Sub
 
Upvote 0
Solution
You're welcome. Just a tip but when you make an edit indent your code correctly, it makes it much easier to spot where the error is in these circumstances.

If you find it hard indenting then download a program called Rubberduck, which is where the old Smart Indenter code resides these days.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top