I cannot for the life of me figure out why this error pops up for one user and not others. Basic gist: I have a user-form that the user fills out, and clicks update. Upon clicking update the form asks "Would you like to update another record?". If the user answers vbYes the line of code is currProvNum.SetFocus to start over. However this error pops up consistently for random users. here is the code around the error, The file is quite big so if it needed I will post it, but for now just the code:
Option Explicit
'Declare module level variables
Dim bNormalProcess As Boolean
Dim facilityNameValid As Boolean
Dim congDistNumValid As Boolean
Dim congDistNameValid As Boolean
Dim netSysIdNameValid As Boolean
Dim netSysIdNumValid As Boolean
Dim custGroupCodeNameValid As Boolean
Dim custGroupCodeNumValid As Boolean
Dim temp As String
'Sets up the userform and defines the values of the drop down menus, then sets Boolean variables
'for the validation at the end of the inputs
Private Sub UserForm_Initialize()
'Initialize variables
Me.updateButton.Enabled = False
Me.cancelButton.Enabled = True
facilityNameValid = False
congDistNumValid = False
congDistNameValid = False
netSysIdNameValid = False
netSysIdNumValid = False
custGroupCodeNameValid = False
bNormalProcess = True
Dim rngColumn1 As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
' Populate combo box list that will expand as the range expands.
Set ws = Worksheets("Master_list")
For Each rngColumn1 In ws.Range("_Col1")
Me.currProvNum.AddItem rngColumn1.Value
Next rngColumn1
'sort control ComboBox Pass the control to the subroutine for sorting
SORTCB Me.currProvNum
'Define the ranges for dropdowns
Me.OpenClosedCombo.List = Worksheets("Sheet2").Range("B1:B2").Value
Application.ScreenUpdating = True
lbl_exit:
Exit Sub
End Sub
'On exit of the first selection box, makes sure there is a value there (even blank) then continues to
'fill the text boxes based on the choice made in the first box.
Private Sub currProvNum_exit(ByVal Cancel As MSForms.ReturnBoolean)
If currProvNum.ListIndex > -1 Then
Dim ws As Worksheet
Set ws = Worksheets("Master_list")
' set the text box values with corresponding places on the spreadsheet based on combo box.
With Me
.facilityName.Value = ws.Cells(.currProvNum.ListIndex + 5, 3)
.congDistNum.Value = ws.Cells(.currProvNum.ListIndex + 5, 4)
.congDistName.Value = ws.Cells(.currProvNum.ListIndex + 5, 5)
.netSysIdNum.Value = ws.Cells(.currProvNum.ListIndex + 5, 6)
.netSysIdName.Value = ws.Cells(.currProvNum.ListIndex + 5, 7)
.custGroupCodeNum.Value = ws.Cells(.currProvNum.ListIndex + 5, 8)
.custGroupCodeName.Value = ws.Cells(.currProvNum.ListIndex + 5, 9)
.OpenClosedCombo.Value = ws.Cells(.currProvNum.ListIndex + 5, 10)
'Store the address values for each control in the .Tag attribute
'(False, False) stores the address as 'A1' instead of '$A$1'
.currProvNum.Tag = ws.Cells(.currProvNum.ListIndex + 5, 1).Address(False, False)
.facilityName.Tag = ws.Cells(.currProvNum.ListIndex + 5, 3).Address(False, False)
.congDistNum.Tag = ws.Cells(.currProvNum.ListIndex + 5, 4).Address(False, False)
.congDistName.Tag = ws.Cells(.currProvNum.ListIndex + 5, 5).Address(False, False)
.netSysIdNum.Tag = ws.Cells(.currProvNum.ListIndex + 5, 6).Address(False, False)
.netSysIdName.Tag = ws.Cells(.currProvNum.ListIndex + 5, 7).Address(False, False)
.custGroupCodeNum.Tag = ws.Cells(.currProvNum.ListIndex + 5, 8).Address(False, False)
.custGroupCodeName.Tag = ws.Cells(.currProvNum.ListIndex + 5, 9).Address(False, False)
.OpenClosedCombo.Tag = ws.Cells(.currProvNum.ListIndex + 5, 10).Address(False, False)
'marks the row - used in testing
temp = CStr(.currProvNum.ListIndex + 5)
facilityName.SetFocus
End With
End If
End Sub
Private Sub cancelButton_Click()
Unload UserForm1
MsgBox "No Changes were made to the selected record."
End Sub
'Runs the routine to put all the information back in the appropriate tagged cells (changed and unchagned)
Private Sub updateButton_Click()
Dim continue As Long
Call PutUserForm1DataBackInWorksheet
Sheets("Master_list").Activate
continue = MsgBox("Would you like to update another record?", vbYesNo)
If continue = vbYes Then
currProvNum.SetFocus
Else
Unload UserForm1
End If
End Sub
'The routine grabs the information from the userform and places them back in the stored cells held by the
' .Tag attribute. Specific command explination blocks below.
Sub PutUserForm1DataBackInWorksheet()
Dim ws As Worksheet
Set ws = Worksheets("Master_list")
Dim sAddress As String
Dim sValue As String
ActiveSheet.Unprotect "Datagen123"
'Get the Worksheet Cell Address
'Get the value from the UserForm
'Put the value in the Worksheet
'currProvNum ComboBox
sAddress = UserForm1.currProvNum.Tag
sValue = Trim(UserForm1.currProvNum.text)
ws.Range(sAddress).Value = sValue
'facilityName
sAddress = UserForm1.facilityName.Tag
sValue = Trim(UserForm1.facilityName.text)
ws.Range(sAddress).Value = sValue
'congDistNum
sAddress = UserForm1.congDistNum.Tag
sValue = Trim(UserForm1.congDistNum.text)
ws.Range(sAddress).Value = sValue
'congDistName
sAddress = UserForm1.congDistName.Tag
sValue = Trim(UserForm1.congDistName.text)
ws.Range(sAddress).Value = sValue
'netSysIdNum
sAddress = UserForm1.netSysIdNum.Tag
sValue = Trim(UserForm1.netSysIdNum.text)
ws.Range(sAddress).Value = sValue
'netSysIdName
sAddress = UserForm1.netSysIdName.Tag
sValue = Trim(UserForm1.netSysIdName.text)
ws.Range(sAddress).Value = sValue
'netSysIdName
sAddress = UserForm1.custGroupCodeNum.Tag
sValue = Trim(UserForm1.custGroupCodeNum.text)
ws.Range(sAddress).Value = sValue
'custGroupCodeNum
sAddress = UserForm1.custGroupCodeName.Tag
sValue = Trim(UserForm1.custGroupCodeName.text)
ws.Range(sAddress).Value = sValue
'Open/Closed Status
sAddress = UserForm1.OpenClosedCombo.Tag
sValue = Trim(UserForm1.OpenClosedCombo.text)
ws.Range(sAddress).Value = sValue
'Get dynamic row and target it at Column AE to datestamp it
sAddress = "Y" + temp
sValue = DateTime.Now
ws.Range(sAddress).Value = sValue
'Get dyanic Row and target it at Column AF to allow for user notes
sAddress = "Z" + temp
sValue = Trim(UserForm1.notesText.text)
ws.Range(sAddress).Value = sValue
Set ws = Nothing
Range("Q1").Select
ActiveSheet.Protect "Datagen123", True, True
End Sub
Bookmarks