Change Header Name on Worksheet Table from a ListBox

[rorobear]

Hello Everyone,

I’m hoping someone can assist me with this small VBA code. I have a workbook with 3 worksheets, each containing a table. There is also 2 listbox, but the listbox of interest is lbxHeaders. It contains the header names of the tables in the 3 worksheets. When a header is selected it populates textbox1 with that name. There’s is also a command button right below. What I’m trying to do is change the name in the textbox1 and have it reflect in the lbxHeader as well as the corresponding table on the worksheet when the button is clicked. I’ve added the workbook for clarity. As always, really appreciate the help.


Option Explicit
Dim NameChange As Worksheet
Dim ColumnChange As String
Private Sub CommandButton1_Click()
'change name of existiing sheet
Dim ColumnName As String
If TextBox1.Text <> vbNullString Then
With ThisWorkbook
'get confirmation of change
If MsgBox("Change Sheet Name To: " & TextBox1.Text & vbCr & _
"Is This New Sheet Name Correct?", vbYesNo, "Change Name") = vbYes Then
ColumnName = TextBox1.Text
Set ColumnChange = ActiveSheet
End If
End With
End If
Call Reset '<---Reset listbox to reflect change
Me.TextBox1.Value = "" '<---clear text after change
Call UserForm_Initialize
End Sub

[rorobear]

hello again All,

Still trying to make this code work, but i realized there were some errors in the code, i tried revising it but still doesn't work. i'm getting compile error: sub function not defined. if anyone can assist, would love the help.


Option ExplicitDim NameChange As Worksheet
Dim HeaderChange As String
Private Sub CommandButton1_Click()
'change name of existiing header
Dim HeaderName As listobject
If TextBox1.Text <> vbNullString Then
With ThisWorkbook

'get confirmation of change
If MsgBox("Change Header Name To: " & TextBox1.Text & vbCr & _
"Is This New Header Name Correct?", vbYesNo, "Change Name") = vbYes Then
listobject(HeaderName).Value = TextBox1.Text
Set HeaderName = ActiveSheet
End If
End With
End If
Call Reset '<---Reset listbox to reflect change
Me.TextBox1.Value = "" '<---clear text after change
Call UserForm_Initialize
End Sub