VBA run time error

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
733
Office Version
  1. 365
Platform
  1. Windows
Hello all

I'm creating a holiday book for work,

In the VBA I've I have a work sheet change function that's adds a comment and changes the font to uppercase to every cell that is edited, that works fine.

But when press delete on a couple of cells while im still playing I get a
runtime error '13' Type Mismatch

Question 1 is there any way I can stop the VBA from crashing?
Question 2 is can I press a F key or something to reset the vba because im having to keep logging out and back in every time

many thanks Jason
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'changes the cells to UPPERCASE
    If Intersect(Target, Range("A1:AH100")) Is Nothing Then Exit Sub
     
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
    
 'Adds a comment box to each cell when somthing is added to the cell

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Target.Comment Is Nothing Then Target.Comment.Delete
Target.AddComment Text:=Application.UserName & vbNewLine & Date & vbNewLine & Time
Target.Comment.Shape.TextFrame.Characters(1, Len(Application.UserName)).Font.Bold = True

     
End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

tygrrboi

Well-known Member
Joined
Sep 8, 2015
Messages
1,196
I can recreate your error by doing something like selecting a bunch of cells in column A and clicking Clear Contents...

To avoid that put the check first before you alter the cells...

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
[COLOR=#ff0000]    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub[/COLOR]
    If Intersect(Target, Range("A1:AH100")) Is Nothing Then Exit Sub
    
'changes the cells to UPPERCASE
    
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
    
 'Adds a comment box to each cell when something is added to the cell




    If Not Target.Comment Is Nothing Then Target.Comment.Delete
    Target.AddComment Text:=Application.UserName & vbNewLine & Date & vbNewLine & Time
    Target.Comment.Shape.TextFrame.Characters(1, Len(Application.UserName)).Font.Bold = True


     
End Sub

Although I do not know why you are checking if it is empty... Wouldn't you want a record of anyone deleting cell contents?
Personally, I would remove the Or IsEmpty(Target) part... but then again I do not know your specific situation...
 
Upvote 0

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
733
Office Version
  1. 365
Platform
  1. Windows
Thank you for your reply

im willing to try anything could you please show me what you mean about the empty removed
 
Upvote 0

tygrrboi

Well-known Member
Joined
Sep 8, 2015
Messages
1,196
Sorry. Its not technical jargon. I thought I was being clear... Sorry for the confusion.

here it is with that part removed.

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
[COLOR=#ff0000]    If Target.Cells.Count > 1 Then Exit Sub[/COLOR]
    If Intersect(Target, Range("A1:AH100")) Is Nothing Then Exit Sub
    
'changes the cells to UPPERCASE
    
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
    
 'Adds a comment box to each cell when something is added to the cell




    If Not Target.Comment Is Nothing Then Target.Comment.Delete
    Target.AddComment Text:=Application.UserName & vbNewLine & Date & vbNewLine & Time
    Target.Comment.Shape.TextFrame.Characters(1, Len(Application.UserName)).Font.Bold = True


     
End Sub
 
Upvote 0

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
733
Office Version
  1. 365
Platform
  1. Windows
Hello I really like the sound of the record keeping

how do I create a log for entry and deleting?

Please help
 
Upvote 0

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
733
Office Version
  1. 365
Platform
  1. Windows
Hi you are being clear its just i'm still a newbie
 
Upvote 0

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
733
Office Version
  1. 365
Platform
  1. Windows
thank you tygrrboi that's works great, I don't get the error now
 
Upvote 0

tygrrboi

Well-known Member
Joined
Sep 8, 2015
Messages
1,196
Just playing around a little I came up with this...

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("A1:AH100")) Is Nothing Then Exit Sub
    
'changes the cells to UPPERCASE


    Dim PreviousEntry As String
    Dim NewEntry As String
    
    Application.EnableEvents = False
    NewEntry = UCase(Target)
    Application.Undo
    PreviousEntry = UCase(Target)
    Target = NewEntry
    Application.EnableEvents = True
    
 'Adds a comment box to each cell when something is added to the cell


    If PreviousEntry = vbNullString Then PreviousEntry = "-blank-"
    If NewEntry = vbNullString Then NewEntry = "-blank-"




    If Target.Comment Is Nothing Then
        Target.AddComment Text:=Application.UserName & " input " & NewEntry & vbNewLine & Date & vbNewLine & Time & vbNewLine
        Target.Comment.Shape.Height = [COLOR=#0000ff]45[/COLOR]
        Target.Comment.Shape.Width =[COLOR=#ff8c00] 110[/COLOR]
[COLOR=#008000]        Target.Comment.Visible = False[/COLOR]
    Else
        With Target.Comment
            .Text PreviousEntry & " was changed to " & NewEntry & vbNewLine & "by: " & Application.UserName & _
                    vbNewLine & Date & vbNewLine & Time & vbNewLine, 1, False
            .Shape.Height = .Shape.Height [COLOR=#ff0000]+ 50[/COLOR]
        End With
    End If



End Sub

If you find that words are getting cut off you can play with the height and width to start...
Also if you think its not extending the size of the comment box enough when you add a new entry, you can increase (or decrease) the height adjustment.

Finally.. I like to have my comments Hidden until you mouse-over them... then they pop up. If you want them to remain there the whole time, delete the part that says
Target.Comment.Visible = False
 
Upvote 0

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
733
Office Version
  1. 365
Platform
  1. Windows
thank you for your help that's fantastic
 
Upvote 0

Forum statistics

Threads
1,191,318
Messages
5,985,950
Members
439,991
Latest member
NCWalker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top