VBA macro, if text+enter then date+shift row down

[Camel123]

Hi,

I am useless and an amateur at VBA and therefore posting this thread

I would like a VBA macro functioning as described below:

Only when inserting text in cell C3 and hitting enter, then today's date appears in B3 and the whole row including all data shifts down. Also I would like to add a filter in row 2.

Any kind soul who could help me out on this?

 

[James006]

Hello Cameltoe ... :wink:

You could test following :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$C$3" Then Exit Sub
Target.Offset(0, -1) = Date
Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Hope this will help

 

[Camel123]

Hello Cameltoe ... :wink:

You could test following :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$C$3" Then Exit Sub
Target.Offset(0, -1) = Date
Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Hope this will help

Dear James, thank you! Only thing missing is the function of integrating filter on the row above. Excel does not allow me to, do you have a work around? Also, I would like to learn basic VBA. What do you guys recommend, youtube tutorials and googling or maybe buy a book?

 

[James006]

Glad to hear the macro is performing as expected ...

When you say

filter on the row above ... Excel does not allow me

Could you be a bit more precise ...

Which row are you talking about ? Do you have field names as headers ? What is Excel not allowing you to do ?

 

[Camel123]

Glad to hear the macro is performing as expected ...

When you say



Could you be a bit more precise ...

Which row are you talking about ? Do you have field names as headers ? What is Excel not allowing you to do ?

So, your macro currently shifts the whole row from row 3 to 4. I would like to add filter to row 2. For instance, in order to find all notes on certain dates without having to scroll down, or search for a key word in the filter and find all notes including that word. When trying to insert filter to row 2 the macro rejects it. Hope it makes it more clear

 

[James006]

Hello,

Adding the Filter to your row 2 can be done beforehand ... manually and once for ever ...

There is no need to include this instruction in your macro which will run each and every time you are making a change ...

Hope this clarifies

 

[Camel123]

Hello,

Adding the Filter to your row 2 can be done beforehand ... manually and once for ever ...

There is no need to include this instruction in your macro which will run each and every time you are making a change ...

Hope this clarifies

In other words, If I would like to remove the filter, for some reason still unknown and then re-added, then I need to inactivate the macro first, then activate it once done?

 

[James006]

In other words ... both topics are totally independent ...

1. Adding or Removing your Filter

2. Using or not Using your macro ...