Search code examples
excelvbauserform

Excel Userform coding revise

I would need help on how to revise the code below. I was able to create the template to enter all the informations needed in the userform when the header is in row 1 on the template. But when I need to relocate the header to row 29. It doesn't work as expected even though I did revised the coded to match with row 29. Please help.

This is a good picture of the header in row1 with the code below. It is working fine. enter image description here

here is the file https://1drv.ms/x/s!AixhKuqjnB1cgW8qhYoRMmt0oN0o?e=W52afT

You will find "Original" Tab. with the original VBA coding working with header in row 1. The "CID" tab will be the one I need to revise the code to work with the header moved to row 29.

This is the original code that work with header in row 1

Sub Refresh_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Original")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

With Me.ListBox1
    .ColumnHeads = True
    .ColumnCount = 12
    .ColumnWidths = "30,100,100,70,100,100,50,100,50,50,120,200"
    
    If last_row = 1 Then
    .RowSource = "Original!A2:L2"
    Else
    .RowSource = "Original!A2:L" & last_row
    End If
    
            
 End With

End Sub

Private Sub Add_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Original")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'Validations---------------------------------------------------------------------------------------
If Me.TextBox1.Value = "" Then
MsgBox "Please Fill Signal Name. If it is not required, fill -", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox2.Value = "" Then
MsgBox "Please Fill (From) Connector REF DES", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox3.Value = "" Then
MsgBox "Please Fill (From) Connector Pin Location", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox4.Value = "" Then
MsgBox "Please Fill Contact P/N or Supplied with Connector", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox5.Value = "" Then
MsgBox "Please Fill Wire Gauge", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox6.Value = "" Then
MsgBox "Please Fill Wire/Cable P/N", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox7.Value = "" Then
MsgBox "Please Fill (To) Connector REF DES", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox8.Value = "" Then
MsgBox "Please Fill (To) Pin Location", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox9.Value = "" Then
MsgBox "Please Fill Contact P/N or Supplied with Connector", vbCritical
Exit Sub
End If
'------------------
If Me.ComboBox10.Value = "" Then
MsgBox "Use Drop Down Arrow to Select Wire Color", vbCritical
Exit Sub
End If
'--------------------------------------------------------------------------------------------------
sh.Range("A" & last_row + 1).Value = "=Row()-1"
sh.Range("B" & last_row + 1).Value = Me.TextBox1.Value
sh.Range("C" & last_row + 1).Value = Me.TextBox2.Value
sh.Range("D" & last_row + 1).Value = Me.TextBox3.Value
sh.Range("E" & last_row + 1).Value = Me.TextBox4.Value
sh.Range("F" & last_row + 1).Value = Me.TextBox5.Value
sh.Range("G" & last_row + 1).Value = Me.TextBox6.Value
sh.Range("H" & last_row + 1).Value = Me.TextBox7.Value
sh.Range("I" & last_row + 1).Value = Me.TextBox8.Value
sh.Range("J" & last_row + 1).Value = Me.TextBox9.Value
sh.Range("K" & last_row + 1).Value = Me.ComboBox10.Value
sh.Range("L" & last_row + 1).Value = Me.TextBox11.Value

'------------------
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.ComboBox10.Value = ""
Me.TextBox11.Value = ""
'------------------
Call Refresh_Data

End Sub""

And this is the picture of the header moved to row 29. enter image description here

Solution

  • Use a constant for the header row and then it's easy to change in the future.

    Option Explicit
Const HEADER = 29

Private Sub CommandButton1_Click()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("CID")

    Dim last_row As Long
    last_row = sh.Cells(Rows.Count, "A").End(xlUp).Row
    If last_row < HEADER Then
        last_row = HEADER
    End If

    Dim arMsg(10) As String, n As Integer, msg As String
    arMsg(1) = "Signal Name. If it is not required, fill -"
    arMsg(2) = "(From) Connector REF DES"
    arMsg(3) = "(From) Connector Pin Location"
    arMsg(4) = "Contact P/N or Supplied with Connector"
    arMsg(5) = "Wire Gauge"
    arMsg(6) = "Wire/Cable P/N"
    arMsg(7) = "(To) Connector REF DES"
    arMsg(8) = "(To) Pin Location"
    arMsg(9) = "Contact P/N or Supplied with Connector"
    arMsg(10) = "Use Drop Down Arrow to Select Wire Color"

    For n = 1 To 9
        If Me.Controls("TextBox" & n).Value = "" Then
             msg = msg & vbLf & n & ") " & arMsg(n)
        End If
    Next
    If Me.Controls("ComboBox10").Value = "" Then
        msg = msg & vbLf & arMsg(10)
    End If

    If Len(msg) > 0 Then
        MsgBox "Please Fill " & msg, vbCritical
        Exit Sub
    End If
  
    Dim c As Control
    With sh.Range("A" & last_row + 1)
        .Offset(0, 0).Value = "=Row()-" & HEADER
        For n = 1 To 11
            If n = 10 Then
                Set c = Me.Controls("ComboBox" & n)
            Else
                Set c = Me.Controls("TextBox" & n)
            End If
           .Offset(0, n).Value = c.Value
           c.Value = ""
        Next
    End With
    Call Refresh_Data(sh)

End Sub

Sub Refresh_Data(sh As Worksheet)

    Dim last_row As Long
    last_row = sh.Cells(Rows.Count, "A").End(xlUp).Row
    
    With Me.ListBox1
        .ColumnHeads = True
        .ColumnCount = 12
        .ColumnWidths = "30,100,100,70,100,100,50,100,50,50,120,200"
        If last_row <= HEADER Then
            last_row = HEADER + 1
        End If
        .RowSource = sh.Name & "!A" & HEADER + 1 & ":L" & last_row
     End With
End Sub