Automatic Day of the week.

[Luke1690]

Someone kindly give me this code yesterday so when i double click D11 it buts the day of the week in the cell in this format ddd.

Private Sub WORKSHEET_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(0, 0) = "D11:J11" Then
Cancel = True
Target.Value = Format(Date, "ddd")
End If
End Sub

it works perfectly on one workbook but i cant seem to get it to work on multiple.
is there a macro or a way i can change this code so its not double click the cell but a button pressed to fill the current day in.
or can someone explain why it wont work on multiple workbooks?

Again, im not an expert so might need to explain.

Thanks Guys

 

[Luke1690]

i have both workbooks open at the moment this code is pasted in both workbooks in Sheet1

sorry ill try and make it more clear.

so i have three order forms saved as below;

WORKBOOK1- SHEET1 - (A11) has a Title of DAY next to that a blank cell (B11) i want the current day to appear in format (WED) when a button is pressed.

WORKBOOK2- SHEET1 - (A11) has a Title of DAY next to that a blank cell (B11) i want the current day to appear in format (WED) when a button is pressed.

WORKBOOK3- SHEET1 - (A11) has a Title of DAY next to that a blank cell (B11) i want the current day to appear in format (WED) when a button is pressed.

I hope that makes more sense, i only need the current day entered once on each workbook, these workbooks are used multiple times a day to order items. so tomorrow when they are opened and the button is pressed (THU) will appear.

i understand merging cells makes it more difficult but the code i was given works on one just not on the other workbooks. if you can supply something to work for the above cells, i will mess around with it to get it to work on my merged cells.

The actual merged cells are Title DAY A11:C11 / BLANK CELL D11:J11

 

[Luke1690]

solved thanks i removed
CANCEL = True
and now it works on multiple workbooks.

Thankyou

 

[smozgur]

solved thanks i removed
CANCEL = True
and now it works on multiple workbooks.

Thankyou

This shouldn't be the reason for the problem. Setting the Cancel parameter as True only stops the actual even action when the event procedure "completed", which is setting focus inside the cell in this event procedure but keeps executing the next lines until the end of the procedure.

The reason of the failure could be either wrong defined merged cells since you are checking the address on a merged area, OR most likely the second workbook didn't have the event procedure set at the time the code was tested.

Regarding your question about making this work with a button: You can assign the following macro to the button.

Sub Button1_Click()
    Worksheets("SheetName").Range("D11").Value = Format(Date, "ddd")
End Sub

Note: "SheetName" in the Worksheets("SheetName") is the name of the worksheet that D11 is supposed be updated and it is not necesary if the button is also on the same worksheet.

 

[Luke1690]

This shouldn't be the reason for the problem. Setting the Cancel parameter as True only stops the actual even action when the event procedure "completed", which is setting focus inside the cell in this event procedure but keeps executing the next lines until the end of the procedure.

The reason of the failure could be either wrong defined merged cells since you are checking the address on a merged area, OR most likely the second workbook didn't have the event procedure set at the time the code was tested.

Regarding your question about making this work with a button: You can assign the following macro to the button.

Sub Button1_Click()
    Worksheets("SheetName").Range("D11").Value = Format(Date, "ddd")
End Sub

Note: "SheetName" in the Worksheets("SheetName") is the name of the worksheet that D11 is supposed be updated and it is not necesary if the button is also on the same worksheet.

Thank you for your reply I'm not a wizard at macros, i mostly copy and paste and debug everything until it works. All i know is when i was messing around with the code and took (Cancel= True) out it worked on all my workbooks.

i even added to it so now when i click on the merged cell above it gives me the current time aswell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(0, 0) = "D11:J11" Then
Target.Value = Format(Date, "ddd")
End If

If Target.Address(0, 0) = "D10:J10" Then
Target.Value = Now()
End If
End Sub

 

[smozgur]

As I explained, removing that line is not the cure that made it work. It is more likely possible that you:

1. Didn't merge the cells (D11:J11)
2. Or didn't have the event procedure copied & pasted yet
3. Or didn't change D11:J11 to D10:J10 in the second workbook

by the time you tested it in the second workbook. I'd be really surprised if it doesn't work when you put that line back. Perhaps you can test it and let us know.

However, removing that Cancel = True line will cause cell to get focus when you double click on the range. So, the cursor will be placed in the cell and it won't be very user friendly.

 

[My Aswer Is This]

As I explained, removing that line is not the cure that made it work. It is more likely possible that you:

1. Didn't merge the cells (D11:J11)
2. Or didn't have the event procedure copied & pasted yet
3. Or didn't change D11:J11 to D10:J10 in the second workbook

by the time you tested it in the second workbook. I'd be really surprised if it doesn't work when you put that line back. Perhaps you can test it and let us know.

However, removing that Cancel = True line will cause cell to get focus when you double click on the range. So, the cursor will be placed in the cell and it won't be very user friendly.

I agree. I have been watching this thread. I have Cancel=True in the script and it works OK for me.

 

[smozgur]

All i know is when i was messing around with the code

We are wondering if you were able to test it by putting Cancel = True back and if it still works as we expected and already tested on our side. In that case post #15 is supposed to be the closest answer and I'd really like to make sure not to suggest an unrelated solution to future readers to avoid leading them in the wrong direction. Please let us know. Thanks.

 

[smozgur]

The solution mark has been removed since it would lead future readers in the wrong direction.

 

[Luke1690]

We are wondering if you were able to test it by putting Cancel = True back and if it still works as we expected and already tested on our side. In that case post #15 is supposed to be the closest answer and I'd really like to make sure not to suggest an unrelated solution to future readers to avoid leading them in the wrong direction. Please let us know. Thanks.

Sorry for not getting back sooner yes the code does work with the cancel=true statement back in. so it was obviously me doing something wrong.

Thankyou everyone

 

[smozgur]

Please do not mark a post that is suggesting removing the Cancel = True solves the problem, because it is not the solution as proved and also explained above.

the code does work with the cancel=true statement back in. so it was obviously me doing something wrong.

Therefore, I am marking post #15 as the solution since you also confirmed it.