Private Sub Worksheet Change - Multiple on same worksheet

poulet · Feb 19th 2017

Re: Private Sub Worksheet Change - Multiple on same worksheet

One more related question.

The date stamp appears in my table and outside my table.

First question

Is there any way to limit the date stamp inside the table?

See full code above...

Here is an extract of the code portion of the date stamp

Code

For Each cell In Target 
             
            If cell.Value <> "" Then 
                 
                Application.EnableEvents = False 
                 
                Range("C" & cell.Row).Value = Now() 
                 
                Application.EnableEvents = True 
                 
            End If 
             
        Next cell

Display More

Second question

My table has data validation which limits the user input between 2 dates for some columns. I also have an input message telling the user how to enter the date when they click on the table column header. When I double click to change the column header title, I get an error with the following portion of the code :

Code

str = Right(str, Len(str) - 1)

The error is Run-Time Error 5 : Invalid Procedure Call or Argument.

I am still able to change the header title but this pop-up always bring me to the VBA debug or end options which is kind of annoying.

Any way to fix this?

Thanks!

poulet · Feb 21st 2017

Re: Private Sub Worksheet Change - Multiple on same worksheet

Ideas?

maria_in_uk · Nov 14th 2020

Hi Everyone!

Could you please help a complete newbie/dummy to combine these two separate codes please? I am a complete newbie and cannot seem to combine two separate codes together. Any help will be much appreciated!

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Not Intersect(Target, Range("H8:L94")) Is Nothing Then
Range("H8:L94").ClearComments 'Clear any existing comments in the range
Range("B23").Value = Target.Row


End If


'Add Formatted Comment with Dynamic Content
If Not Intersect(Target, Range("H8:K11,H19:J21,H26:J33,H37:J42,H50:J53,H59:J60")) Is Nothing Then createFormattedComment_rev


(what shall I write here?)


With ActiveWindow.VisibleRange
ActiveSheet.Shapes("PictureX").Top = .Top + 5
ActiveSheet.Shapes("PictureX").Left = .Left + .Width - ActiveSheet.Shapes("PictureX").Width - 870
ActiveSheet.Shapes("PictureX").Top = .Top + 35
ActiveSheet.Shapes("PictureX").Left = .Left + .Width - ActiveSheet.Shapes("PictureX").Width - 870
End With
End Sub

Display More

THANK YOU!

**royUK** · Nov 14th 2020

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Note: no apostrophe in the tags, just used for demonstration here.

['code]

your code goes between these tags

['/code]

Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Nov 14th 2020

It doesn't look like you need anything adding.

Private Sub Worksheet Change - Multiple on same worksheet

Participate now!

Share