rorobear
02-15-2023, 07:11 AM
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
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