davis1118
03-01-2018, 03:27 PM
I will try to explain this the best I can. I am not receiving any vba errors, but my data is not properly loading into theworksheet from the userform.
I have a userform with comboxes and text boxes. The userform copies a MASTER sheet and then renames the new sheet based on textbox values on the userform. On the new sheet, there are textboxes, and togglebuttons (All controls on worksheet are ActiveX). The textboxes on the new sheet get populated from the userform. Then the textboxes on the sheet change the togglebutton captions and other textbox values on the sheet.
The problem I am having is the first two textboxes on the worksheet that get updated from the userform aren't updating. They are grayed out, and if I move the cursor over them, excel crashes. This problem doesn’t happen at home with Excel 2007, only at work with Excel 2013.
I’m thinking I have used the wrong syntax for 2013?? I have include the parts of the code from the userform inputting the data to thenew worksheet, and then the worksheet change code. Please feel free to give mepointers. I am not the best at writing the code, but always enjoy learning howto write it better. Thank you for thehelp.
USERFORM
Private Sub OKButton1_Click()
Application.ScreenUpdating = False
Dim nmbrLastRow As Long
Dim prtLastRow As Long
Dim wsList As Worksheet
Dim shtname As String
Dim nmbrrng As Range
Dim prtrng As Range
Dim actvsht As String
Set wsList = Sheet30
With wsList
nmbrLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
prtLastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
Set nmbrrng = .Cells(nmbrLastRow + 1, "D")
Set prtrng = .Cells(prtLastRow + 1, "E")
End With
shtname = Me.FinalNumberBox
'COPY NAME THE NEW SHEET
Sheet28.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = shtname
'INPUT HEADER, PROCESS, & TOGGLEBUTTON INFO
Sheets(shtname).Range("A10") = Me.FinalNumberBox.Value
Sheets(shtname).Range("A11") = Me.FinalPartBox.Value
Sheets(shtname).HeaderBox1.Value = Me.FinalPartBox & " " & "#" & Me.FinalNumberBox
Sheets(shtname).ProcessBox1.Value = Me.ComboBox1.Value
If Me.ComboBox1.Value = "" Then Sheets(shtname).ToggleButton1.Visible = False _
Else Sheets(shtname).ToggleButton1.Visible = True
Sheets(shtname).ProcessBox2.Value = Me.ComboBox2.Value
If Me.ComboBox2.Value = "" Then Sheets(shtname).ToggleButton2.Visible = False _
Else Sheets(shtname).ToggleButton2.Visible = True
'INPUT SUB PART NUMBER INFO
Sheets(shtname).PartBox1.Value = Me.TextBox1.Value
Sheets(shtname).PartBox2.Value = Me.TextBox2.Value
'ADD HYPERLINK TO PRODUCT PAGE
With wsList
.Hyperlinks.Add Anchor:=nmbrrng, Address:="", SubAddress:=shtname & "!A1", TextToDisplay:=FinalNumberBox.Value
.Hyperlinks.Add Anchor:=prtrng, Address:="", SubAddress:=shtname & "!A1", TextToDisplay:=FinalPartBox.Value
End With
With nmbrrng.Font
.ColorIndex = xlAutomatic
.Underline = xlUnderlineStyleNone
.Name = "Calibri"
.Size = 18
.Bold = True
End With
With prtrng.Font
.ColorIndex = xlAutomatic
.Underline = xlUnderlineStyleNone
.Name = "Calibri"
.Size = 18
.Bold = True
End With
Application.ScreenUpdating = True
Unload Me
End Sub
COPIED WORKSHEET
'PROCESS CHANGES
Private Sub ProcessBox1_Change()
ToggleButton1.Caption = ProcessBox1.Value & Chr(10) & "#" & PartBox1.Value
ProcessHdr1.Value = ProcessBox1.Value & " - " & "#" & PartBox1.Value
End Sub
Private Sub ProcessBox2_Change()
ToggleButton2.Caption = ProcessBox2.Value & Chr(10) & "#" & PartBox2.Value
ProcessHdr2.Value = ProcessBox2.Value & " - " & "#" & PartBox2.Value
End Sub
'PART NUMBER CHANGES
Private Sub PartBox1_Change()
ToggleButton1.Caption = ProcessBox1.Value & Chr(10) & "#" & PartBox1.Value
ProcessHdr1.Value = ProcessBox1.Value & " - " & "#" & PartBox1.Value
End Sub
Private Sub PartBox2_Change()
ToggleButton2.Caption = ProcessBox2.Value & Chr(10) & "#" & PartBox2.Value
ProcessHdr2.Value = ProcessBox2.Value & " - " & "#" & PartBox2.Value
End Sub
I have a userform with comboxes and text boxes. The userform copies a MASTER sheet and then renames the new sheet based on textbox values on the userform. On the new sheet, there are textboxes, and togglebuttons (All controls on worksheet are ActiveX). The textboxes on the new sheet get populated from the userform. Then the textboxes on the sheet change the togglebutton captions and other textbox values on the sheet.
The problem I am having is the first two textboxes on the worksheet that get updated from the userform aren't updating. They are grayed out, and if I move the cursor over them, excel crashes. This problem doesn’t happen at home with Excel 2007, only at work with Excel 2013.
I’m thinking I have used the wrong syntax for 2013?? I have include the parts of the code from the userform inputting the data to thenew worksheet, and then the worksheet change code. Please feel free to give mepointers. I am not the best at writing the code, but always enjoy learning howto write it better. Thank you for thehelp.
USERFORM
Private Sub OKButton1_Click()
Application.ScreenUpdating = False
Dim nmbrLastRow As Long
Dim prtLastRow As Long
Dim wsList As Worksheet
Dim shtname As String
Dim nmbrrng As Range
Dim prtrng As Range
Dim actvsht As String
Set wsList = Sheet30
With wsList
nmbrLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
prtLastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
Set nmbrrng = .Cells(nmbrLastRow + 1, "D")
Set prtrng = .Cells(prtLastRow + 1, "E")
End With
shtname = Me.FinalNumberBox
'COPY NAME THE NEW SHEET
Sheet28.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = shtname
'INPUT HEADER, PROCESS, & TOGGLEBUTTON INFO
Sheets(shtname).Range("A10") = Me.FinalNumberBox.Value
Sheets(shtname).Range("A11") = Me.FinalPartBox.Value
Sheets(shtname).HeaderBox1.Value = Me.FinalPartBox & " " & "#" & Me.FinalNumberBox
Sheets(shtname).ProcessBox1.Value = Me.ComboBox1.Value
If Me.ComboBox1.Value = "" Then Sheets(shtname).ToggleButton1.Visible = False _
Else Sheets(shtname).ToggleButton1.Visible = True
Sheets(shtname).ProcessBox2.Value = Me.ComboBox2.Value
If Me.ComboBox2.Value = "" Then Sheets(shtname).ToggleButton2.Visible = False _
Else Sheets(shtname).ToggleButton2.Visible = True
'INPUT SUB PART NUMBER INFO
Sheets(shtname).PartBox1.Value = Me.TextBox1.Value
Sheets(shtname).PartBox2.Value = Me.TextBox2.Value
'ADD HYPERLINK TO PRODUCT PAGE
With wsList
.Hyperlinks.Add Anchor:=nmbrrng, Address:="", SubAddress:=shtname & "!A1", TextToDisplay:=FinalNumberBox.Value
.Hyperlinks.Add Anchor:=prtrng, Address:="", SubAddress:=shtname & "!A1", TextToDisplay:=FinalPartBox.Value
End With
With nmbrrng.Font
.ColorIndex = xlAutomatic
.Underline = xlUnderlineStyleNone
.Name = "Calibri"
.Size = 18
.Bold = True
End With
With prtrng.Font
.ColorIndex = xlAutomatic
.Underline = xlUnderlineStyleNone
.Name = "Calibri"
.Size = 18
.Bold = True
End With
Application.ScreenUpdating = True
Unload Me
End Sub
COPIED WORKSHEET
'PROCESS CHANGES
Private Sub ProcessBox1_Change()
ToggleButton1.Caption = ProcessBox1.Value & Chr(10) & "#" & PartBox1.Value
ProcessHdr1.Value = ProcessBox1.Value & " - " & "#" & PartBox1.Value
End Sub
Private Sub ProcessBox2_Change()
ToggleButton2.Caption = ProcessBox2.Value & Chr(10) & "#" & PartBox2.Value
ProcessHdr2.Value = ProcessBox2.Value & " - " & "#" & PartBox2.Value
End Sub
'PART NUMBER CHANGES
Private Sub PartBox1_Change()
ToggleButton1.Caption = ProcessBox1.Value & Chr(10) & "#" & PartBox1.Value
ProcessHdr1.Value = ProcessBox1.Value & " - " & "#" & PartBox1.Value
End Sub
Private Sub PartBox2_Change()
ToggleButton2.Caption = ProcessBox2.Value & Chr(10) & "#" & PartBox2.Value
ProcessHdr2.Value = ProcessBox2.Value & " - " & "#" & PartBox2.Value
End Sub