breynolds0431
Active Member
- Joined
- Feb 15, 2013
- Messages
- 303
- Office Version
- 365
- 2016
- Platform
- Windows
I have a userform that will write the input data to a sheet after each textbox_exit. I did this to avoid having a Submit and Cancel button. The textbox_exit will check spelling (just copies contents to a sheet cell and runs .CheckSpelling on that range. I also have a label that once clicked with write a signoff to the sheet where the texbox_exit edits are made. If an edit is made to the textbox after a signoff, the signoff will be cleared - this is actually done through a worksheet_change specific to the row being edited.
Here's the problem... If a user is in a textbox and makes no changes but uses the mouse to click the signoff image, then the .CheckSpelling process will still run creating an edit and clearing the signoff. It basically writes the signoff info and then deletes it after the spell check finishes.
Below is the spell check for the textbox_exit and the img_click for the signoff. Is there a way to recognize when img_Click occurs from the textbox_exit so the .CheckSpelling doesn't run?
Here's the problem... If a user is in a textbox and makes no changes but uses the mouse to click the signoff image, then the .CheckSpelling process will still run creating an edit and clearing the signoff. It basically writes the signoff info and then deletes it after the spell check finishes.
Below is the spell check for the textbox_exit and the img_click for the signoff. Is there a way to recognize when img_Click occurs from the textbox_exit so the .CheckSpelling doesn't run?
VBA Code:
Private Sub tbComments_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dSheet As Worksheet: Set dSheet = ThisWorkbook.Sheets("UDR SOIs")
Dim sCheck As Worksheet: Set sCheck = ThisWorkbook.Sheets("SpellCheck")
Application.ScreenUpdating = False
With sCheck
.Range("C2").Value = Me.tbComments
.Range("C2").CheckSpelling SpellLang:=1033
Me.tbComments = .Range("C2").Value
.Range("C2").ClearContents
End With
Dim lRow As Long: lRow = dSheet.Cells(dSheet.Rows.Count, "A").End(xlUp).Row
Dim sRange As Range
Dim FindRow As Range
Set sRange = dSheet.Range(dSheet.Cells(15, 1), dSheet.Cells(lRow, 1))
Set FindRow = sRange.Find(Me.cbNoteNo, LookIn:=xlValues, lookat:=xlWhole)
If Me.cbNoteNo.Value = "" Then
Application.ScreenUpdating = True
Exit Sub
Else
With dSheet
.Unprotect
.Cells(FindRow.Row, 7).Value = Me.tbComments
.Protect
End With
End If
Application.ScreenUpdating = True
End Sub
Private Sub imgL2_Click()
If Me.cbNoteNo.Value = "" Then Exit Sub
Dim dSheet As Worksheet: Set dSheet = ThisWorkbook.Sheets("UDR SOIs")
Dim lRow As Long: lRow = dSheet.Cells(dSheet.Rows.Count, "A").End(xlUp).Row
Dim sRange As Range
Dim FindRow As Range
Set sRange = dSheet.Range(dSheet.Cells(15, 1), dSheet.Cells(lRow, 1))
Set FindRow = sRange.Find(Me.cbNoteNo, LookIn:=xlValues, lookat:=xlWhole)
Dim fRow As Long: fRow = FindRow.Row
With dSheet
.Unprotect
.Cells(fRow, 10).Value = Environ("username") & " " & format(Now, "mm/dd/yy")
.Protect
End With
Unload Me
End Sub