0

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

Improve this question
4
  • Did you change Set sh = ThisWorkbook.Sheets("Original") in sub Add_Click() ? Commented Oct 23, 2021 at 15:57
  • You have blank cells above the header in column A so Application.WorksheetFunction.CountA(sh.Range("A:A")) no longer finds the last row. Use sh.Cells(Rows.Count,"A").End(xlUp).Row Commented Oct 23, 2021 at 16:59
  • The the code in "Original" Tab is working good. It is an example. The "CID" Tab is the one need to revise the code. Commented Oct 25, 2021 at 14:27
  • Yes, I am aware of the blank cell above header column A. I need learn how to revise the code to work with all the blank cell above the header. Do you want me to replace "Application.WorksheetFunction.CountA(sh.Range("A:A"))" with sh.Cells(Rows.Count,"A").End(xlUp).Row"? how about the rest of the cod.e? Commented Oct 25, 2021 at 14:29

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Thanks CDP1802, Where do you want me to place your code in "CID" tab coding? Do you want me to replace all my code with your? Or is there a specific place you want me to place it in? Sorry I am not familiar with your code. Much appreciate if you could show me.
@Ryan Put on CID sheet to replace your code, rename CommandButton1_Click() to Add_Click()
Wow thank CDP1802, I am able to add new line under the header line 30 now. Also, it is show up in the listbox1 perfectly. That is good. So on the previous code, I am able to double click any line on the listbox1 to show on all the textbox for update, and delete. I wonder how can we include all the in your code. Also, the save and clear button code is missing from your code. I am not sure if can copy and paste those code from the original code over.
@ryan yes keep your code for the other button click events and the listbox double click event.
@ryan Problem seems to be adding/deleting from the table when it is a rowsource for the listbox so I have removed it before the add/delete and reapplied it after . link here
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.