Hi all, I have a userform with a textbox that I load from a standard module that I use to edit cell comments more easily than Excel's comment boxes. Everything works (ie. changing cells and the textbox updating to the new cell's comment, automatic storing of the textbox data to the previous cell's comment. What doesn't work is when a cell has no comment, and I add one to the textbox and then click on a different cell, I get thrown a Object variable or with block not set on the line bolded below.
Also, in a standard module elsewhere, I set the public variable PrevCellRange to the active cell upon load of the userform as follows:
PrevCellRange = Application.ActiveCell.Address
Then in my worksheet module, I have the following main code, with public variables set atop the module:
Public PrevCellRange As String
Public NewCell As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
PrevCellRange = NewCell
NewCell = ActiveCell.Address
If myUserForm.myTextBox <> vbNullString And Range("userFormIsLoaded") = 1 Then
If Range(PrevCellRange).Comment.Text <> vbNullString Then
Range(PrevCellRange).Comment.Delete
End If
Range(PrevCellRange).AddComment.Text Text:=myUserForm.myTextBox.Text
End If
'Refresh Text Box with Cell Change
If Application.ActiveCell.Comment Is Nothing Then
myUserForm.myTextBox.Text = ""
Else
myUserForm.myTextBox.Text = Application.ActiveCell.Comment.Text
End If
Thanks all for your help again, this is causing me to pull my hair out. Frank
Bookmarks