Customising date format
 

After entering a date as say 2/8/16 it's easy to format it to become
Tuesday 2 Aug 2016 by using 'dddd d mmmm yyyy'. But is it possible to
refine dates further to
Tuesday 2nd August 2016
Wednesday 3rd August 2016
etc ?

Now using Excel 365 on my new Windows 10 PC, after 15 years with XP.
(Not happy at having to pay about £5/month for my occasional use, as I
could not get Excel 2000 from Office Pro 2000 working.)

--
Terry, East Grinstead, UK

Customising date format
 

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

After entering a date as say 2/8/16 it's easy to format it to become
Tuesday 2 Aug 2016 by using 'dddd d mmmm yyyy'. But is it possible to
refine dates further to
Tuesday 2nd August 2016
Wednesday 3rd August 2016
etc ?

if you insert the dates in column A then try this Worksheet_Change
event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or _
Target.Count 1 Then Exit Sub

If IsDate(Target) Then
Select Case Day(Target)
Case 1, 11, 21, 31
Target.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
Target.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
Target.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
Target.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

After entering a date as say 2/8/16 it's easy to format it to become
Tuesday 2 Aug 2016 by using 'dddd d mmmm yyyy'. But is it possible
to refine dates further to
Tuesday 2nd August 2016
Wednesday 3rd August 2016
etc ?

if you insert the dates in column A then try this Worksheet_Change
event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or _
Target.Count 1 Then Exit Sub

If IsDate(Target) Then
Select Case Day(Target)
Case 1, 11, 21, 31
Target.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
Target.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
Target.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
Target.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
End Sub


Regards
Claus B.

11 should use "th" (11th) not "st" (11st)...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Customising date format
 

Hi Garry,

Am Sun, 21 Aug 2016 13:24:04 -0400 schrieb GS:

11 should use "th" (11th) not "st" (11st)...

you are right. Thank you for improving my mistake.


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

Now using Excel 365 on my new Windows 10 PC, after 15 years with XP.
(Not happy at having to pay about £5/month for my occasional use, as I
could not get Excel 2000 from Office Pro 2000 working.)

with Excel 365 you can also use CF for formatting the dates.
Format all cells with
dddd d"th" MMMM YYYY
For the special dates then use CF.
E.g. for 1, 21 and 31:
New Rule = Formula to determine...:
=OR(DAY(D1)=1,DAY(D1)=21,DAY(D1)=31)
and format:
dddd d"st" MMMM YYYY
Add new rules for
2, 22 and 3, 23


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

Claus Busch wrote:

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

After entering a date as say 2/8/16 it's easy to format it to become
Tuesday 2 Aug 2016 by using 'dddd d mmmm yyyy'. But is it possible to
refine dates further to
Tuesday 2nd August 2016
Wednesday 3rd August 2016
etc ?

if you insert the dates in column A then try this Worksheet_Change
event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or _
Target.Count 1 Then Exit Sub

If IsDate(Target) Then
Select Case Day(Target)
Case 1, 11, 21, 31
Target.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
Target.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
Target.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
Target.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
End Sub


Regards
Claus B.

Thanks both.

In no-brainer terms, could you describe the steps I should take after
copying that code to the clipboard please? Is that just the same as a
macro?

--
Terry, East Grinstead, UK

Customising date format
 

Hi Terry,

Am Mon, 22 Aug 2016 17:19:06 +0100 schrieb Terry Pinnell:

In no-brainer terms, could you describe the steps I should take after
copying that code to the clipboard please? Is that just the same as a
macro?

press Alt+F11 = In Project Explorer double click on the expected sheet
and paste the code in the code window.


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

Claus Busch wrote:

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

Now using Excel 365 on my new Windows 10 PC, after 15 years with XP.
(Not happy at having to pay about £5/month for my occasional use, as I
could not get Excel 2000 from Office Pro 2000 working.)

with Excel 365 you can also use CF for formatting the dates.
Format all cells with
dddd d"th" MMMM YYYY
For the special dates then use CF.
E.g. for 1, 21 and 31:
New Rule = Formula to determine...:
=OR(DAY(D1)=1,DAY(D1)=21,DAY(D1)=31)
and format:
dddd d"st" MMMM YYYY
Add new rules for
2, 22 and 3, 23


Regards
Claus B.

For me, that seems the easiest method, at least for relatively few
entries.

But, without getting into complicated conditional formatting, can I
not now simply edit the few changes needed? Using F2 on cell C11 for
example displays '31/07/2016'. Must I delete those cells and re-enter
from scratch? Or, as I've done for now, capture them with an OCR tool,
paste and edit?

https://dl.dropboxusercontent.com/u/...el-Dates-2.jpg

I also tried to use one of the Paste Special options, unsuccessfully.

--
Terry, East Grinstead, UK

Customising date format
 

Hi Terry,

Am Mon, 22 Aug 2016 17:58:51 +0100 schrieb Terry Pinnell:

But, without getting into complicated conditional formatting, can I
not now simply edit the few changes needed? Using F2 on cell C11 for
example displays '31/07/2016'. Must I delete those cells and re-enter
from scratch? Or, as I've done for now, capture them with an OCR tool,
paste and edit?

https://dl.dropboxusercontent.com/u/...el-Dates-2.jpg

it looks like you have all dates formatted with
dddd d"th" MMMM YYYY
That is correct. Select your dates = CF = New Rule = Formula to
determine...
Insert the formula and format as expected.
Download he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
the workbook "DateFormat" and have a look at the custom numberformat and
the conditional formating.


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

Hi again,

Am Mon, 22 Aug 2016 19:16:59 +0200 schrieb Claus Busch:

it looks like you have all dates formatted with
dddd d"th" MMMM YYYY
That is correct. Select your dates = CF = New Rule = Formula to
determine...
Insert the formula and format as expected.
Download he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
the workbook "DateFormat" and have a look at the custom numberformat and
the conditional formating.

I hope the formats will be translated when you open the file.
if not
tttt t"st" MMMM JJJJ means
dddd d"st" MMMM YYYY


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

Claus Busch wrote:

Hi Terry,

Am Mon, 22 Aug 2016 17:19:06 +0100 schrieb Terry Pinnell:

In no-brainer terms, could you describe the steps I should take after
copying that code to the clipboard please? Is that just the same as a
macro?

press Alt+F11 = In Project Explorer double click on the expected sheet
and paste the code in the code window.


Regards
Claus B.

Thanks Claus. I chose 'This Workbook', pasted it, edited out the 11 in
Case 1, and returned to the worksheet. But how do I now apply it
please? Where do I access it in this complex Excel 365 UI? I found my
macros, but it's not in that list.

https://dl.dropboxusercontent.com/u/...el-Dates-3.jpg

I'm new to this version after 16 years with Excel 2000, the last
decade or so of which was very infrequent use, so thanks for your
patience ;-)

--
Terry, East Grinstead, UK

Customising date format
 

Hi Terry,

Am Mon, 22 Aug 2016 18:23:37 +0100 schrieb Terry Pinnell:

Thanks Claus. I chose 'This Workbook', pasted it, edited out the 11 in
Case 1, and returned to the worksheet. But how do I now apply it
please? Where do I access it in this complex Excel 365 UI? I found my
macros, but it's not in that list.

the code must be placed in the code module of the expected /worksheet/
not in ThisWorkbook.
Your dates will be formatted when you enter e.g.
23/7/16
Download from he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
the workbook "DateFormat".
There is the VBA solution as well the CF solution.


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

I'm new to this version after 16 years with Excel 2000, the last
decade or so of which was very infrequent use, so thanks for your
patience

The VBE has remained the same since v2000. Beginning in v2010 VBA7 is
used; VBA6 persists for v2000 to v2007.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Customising date format
 

Claus Busch wrote:

Hi Terry,

Am Mon, 22 Aug 2016 18:23:37 +0100 schrieb Terry Pinnell:

Thanks Claus. I chose 'This Workbook', pasted it, edited out the 11 in
Case 1, and returned to the worksheet. But how do I now apply it
please? Where do I access it in this complex Excel 365 UI? I found my
macros, but it's not in that list.

the code must be placed in the code module of the expected /worksheet/
not in ThisWorkbook.
Your dates will be formatted when you enter e.g.
23/7/16
Download from he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
the workbook "DateFormat".
There is the VBA solution as well the CF solution.


Regards
Claus B.


Got it, thanks! Well, I *think* so.

I re-pasted it correctly into the specific worksheet. Then I found
that I could type 23/7/16 into col A and automatically get Saturday
23rd July 2016.

To do this on many entries I use the format painter, yes?

With a fresh worksheet in that workbook I was pleased to see that it
still worked. But if I closed and re-opened Excel and started a fresh
workbook, how would I get it working then?

For time being I'll pass on the CF method.

Much appreciate your unstinting help, Claus.


--
Terry, East Grinstead, UK

Customising date format
 

GS wrote:

I'm new to this version after 16 years with Excel 2000, the last
decade or so of which was very infrequent use, so thanks for your
patience

The VBE has remained the same since v2000. Beginning in v2010 VBA7 is
used; VBA6 persists for v2000 to v2007.

Thanks Gary, that's true, the VBE seems to be the only familiar
environment! It's the main app that I find daunting. Is there any way
to get a sort of 'classic' or 'legacy' look, with the familiar menus?
At least until I get more comfortable using the numerous and densely
populated 'ribbons'?

--
Terry, East Grinstead, UK

Customising date format
 

Hi Terry,

Am Mon, 22 Aug 2016 19:07:06 +0100 schrieb Terry Pinnell:

I re-pasted it correctly into the specific worksheet. Then I found
that I could type 23/7/16 into col A and automatically get Saturday
23rd July 2016.

To do this on many entries I use the format painter, yes?

no, each entry in column A will be formatted.

With a fresh worksheet in that workbook I was pleased to see that it
still worked. But if I closed and re-opened Excel and started a fresh
workbook, how would I get it working then?

In a new workbook you have to paste the code again. The code only works
in the book it is inserted.


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

Hi Terry,

Am Mon, 22 Aug 2016 19:07:06 +0100 schrieb Terry Pinnell:

With a fresh worksheet in that workbook I was pleased to see that it
still worked. But if I closed and re-opened Excel and started a fresh
workbook, how would I get it working then?

you can also use a simple macro and place it in the PERSONAL.xlsb. Then
you can work with it in each workbook.
Enter your dates like 23/7/16, select you dates and run following macro:

Sub FormatDate()
Dim rngC As Range

For Each rngC In Selection
If IsDate(rngC) Then
Select Case Day(rngC)
Case 1, 21, 31
rngC.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
rngC.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
rngC.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
rngC.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

GS wrote:

I'm new to this version after 16 years with Excel 2000, the last
decade or so of which was very infrequent use, so thanks for your
patience

The VBE has remained the same since v2000. Beginning in v2010 VBA7
is used; VBA6 persists for v2000 to v2007.

Thanks Gary, that's true, the VBE seems to be the only familiar
environment! It's the main app that I find daunting. Is there any way
to get a sort of 'classic' or 'legacy' look, with the familiar menus?
At least until I get more comfortable using the numerous and densely
populated 'ribbons'?

Yep.., J-Walk has offered this since v2007...


Sub ToggleOldMenus(Reset As Boolean) 'adapted from sample by J-Walk
' Adds the early Menubar and Standard/Formatting bars to XL12+
If Application.VERSION < 12 Then Exit Sub
Dim cBar As CommandBar, ctl As CommandBarControl, sz, vBars, v, n&,
j&

Const sOldBars$ = "Built-In
Menus|OldMenus,Standard|EarlyStandard,Formatting|E arlyFormatting"
vBars = Split(sOldBars, ",")

For n = LBound(vBars) To UBound(vBars)
v = Split(vBars(n), "|")
'Delete it, if it exists
On Error Resume Next: CommandBars(v(1)).Delete: On Error GoTo 0

If Reset Then
' Set cBar = CommandBars.Add(v(1)) '//puts bars on separate rows
'Puts Standard/Formatting controls on the same row
If n < 2 Then Set cBar = CommandBars.Add(v(1))

If cBar.name = "OldMenus" Then
GoTo addBuiltinMenus
Else
For j = 1 To CommandBars(v(0)).Controls.Count
CommandBars(v(0)).Controls(j).Copy cBar
Next 'j
End If 'cBar.name = "OldMenus"
moreCbars:
cBar.Visible = True
End If 'Reset
Next 'n

normalexit:
Exit Sub

addBuiltinMenus:
'Copy the controls from Excel's "Built-in Menus" shortcut menu
With CommandBars("Built-in Menus")
.Controls("&File").Copy cBar
.Controls("&Edit").Copy cBar
.Controls("&View").Copy cBar
.Controls("&Insert").Copy cBar
.Controls("F&ormat").Copy cBar
.Controls("&Tools").Copy cBar
.Controls("&Data").Copy cBar
.Controls("&Window").Copy cBar
.Controls("&Help").Copy cBar
End With
GoTo moreCbars
End Sub

...which will persist until you delete them.

To create: ToggleOldMenus True

To remove: ToggleOldMenus False

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Customising date format
 

Claus Busch wrote:

Hi Terry,

Am Mon, 22 Aug 2016 19:07:06 +0100 schrieb Terry Pinnell:

With a fresh worksheet in that workbook I was pleased to see that it
still worked. But if I closed and re-opened Excel and started a fresh
workbook, how would I get it working then?

you can also use a simple macro and place it in the PERSONAL.xlsb. Then
you can work with it in each workbook.
Enter your dates like 23/7/16, select you dates and run following macro:

Sub FormatDate()
Dim rngC As Range

For Each rngC In Selection
If IsDate(rngC) Then
Select Case Day(rngC)
Case 1, 21, 31
rngC.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
rngC.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
rngC.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
rngC.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
Next
End Sub


Regards
Claus B.

As you see from this screenshot, I'm plainly doing something wrong.
Neither your code nor that from GS to change the 'theme' is working.

https://dl.dropboxusercontent.com/u/...cel-VBA-01.jpg

--
Terry, East Grinstead, UK

Customising date format
 

Hi Terry,

Am Thu, 25 Aug 2016 16:54:44 +0100 schrieb Terry Pinnell:

As you see from this screenshot, I'm plainly doing something wrong.
Neither your code nor that from GS to change the 'theme' is working.

download from he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
the workbook "DateFormat"
There is a solution for Worksheet_Change event, for working with simple
macro and for CF.


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

GS wrote:

GS wrote:

I'm new to this version after 16 years with Excel 2000, the last
decade or so of which was very infrequent use, so thanks for your
patience

The VBE has remained the same since v2000. Beginning in v2010 VBA7
is used; VBA6 persists for v2000 to v2007.

Thanks Gary, that's true, the VBE seems to be the only familiar
environment! It's the main app that I find daunting. Is there any way
to get a sort of 'classic' or 'legacy' look, with the familiar menus?
At least until I get more comfortable using the numerous and densely
populated 'ribbons'?

Yep.., J-Walk has offered this since v2007...


Sub ToggleOldMenus(Reset As Boolean) 'adapted from sample by J-Walk
' Adds the early Menubar and Standard/Formatting bars to XL12+
If Application.VERSION < 12 Then Exit Sub
Dim cBar As CommandBar, ctl As CommandBarControl, sz, vBars, v, n&,
j&

Const sOldBars$ = "Built-In
Menus|OldMenus,Standard|EarlyStandard,Formatting| EarlyFormatting"
vBars = Split(sOldBars, ",")

For n = LBound(vBars) To UBound(vBars)
v = Split(vBars(n), "|")
'Delete it, if it exists
On Error Resume Next: CommandBars(v(1)).Delete: On Error GoTo 0

If Reset Then
' Set cBar = CommandBars.Add(v(1)) '//puts bars on separate rows
'Puts Standard/Formatting controls on the same row
If n < 2 Then Set cBar = CommandBars.Add(v(1))

If cBar.name = "OldMenus" Then
GoTo addBuiltinMenus
Else
For j = 1 To CommandBars(v(0)).Controls.Count
CommandBars(v(0)).Controls(j).Copy cBar
Next 'j
End If 'cBar.name = "OldMenus"
moreCbars:
cBar.Visible = True
End If 'Reset
Next 'n

normalexit:
Exit Sub

addBuiltinMenus:
'Copy the controls from Excel's "Built-in Menus" shortcut menu
With CommandBars("Built-in Menus")
.Controls("&File").Copy cBar
.Controls("&Edit").Copy cBar
.Controls("&View").Copy cBar
.Controls("&Insert").Copy cBar
.Controls("F&ormat").Copy cBar
.Controls("&Tools").Copy cBar
.Controls("&Data").Copy cBar
.Controls("&Window").Copy cBar
.Controls("&Help").Copy cBar
End With
GoTo moreCbars
End Sub

..which will persist until you delete them.

To create: ToggleOldMenus True

To remove: ToggleOldMenus False

Thanks, but after much fiddling I still cannot get it working. This
screenshot shows the steps I took after copying your code to the
clipboard and opening Excel 365.

https://dl.dropboxusercontent.com/u/...cel-VBA-01.jpg

Nor could I get Claus's latest code working. In both cases I suspect
it's something to do with Personal.XLS (not Personal.XLSb which Claus
mentioned).

I am beginning to loathe Excel 365, as it's taking me minutes to find
things that I did in a second with Excel 2000. So I'm desperate to get
as close to an old or oldish version as possible!

Can you step me through exactly what I do with your code, based on
what you see in my screenshot please? I'm sure it's painfully obvious
but I don't mind being embarrassed ;-)

And if anyone knows of a good guide for users like me, jumping N
versions to 365, I'd welcome a heads up please.


--
Terry, East Grinstead, UK

Customising date format
 

Claus Busch wrote:

Hi Terry,

Am Thu, 25 Aug 2016 16:54:44 +0100 schrieb Terry Pinnell:

As you see from this screenshot, I'm plainly doing something wrong.
Neither your code nor that from GS to change the 'theme' is working.

download from he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
the workbook "DateFormat"
There is a solution for Worksheet_Change event, for working with simple
macro and for CF.


Regards
Claus B.

Thanks Claus but the issue is clearly more fundamental, something
basically wrong I'm doing about pasting your code. I have your
previous code working in one specific worksheet. It's now a matter of
making it universal. (In fact I don't see why the code has changed?)

And anyway, after downloading that DateFormat workbook, what is its
purpose? What am I supposed to do with it?

--
Terry, East Grinstead, UK

Customising date format
 

Hi Terry,

Am Thu, 25 Aug 2016 17:48:34 +0100 schrieb Terry Pinnell:

Thanks Claus but the issue is clearly more fundamental, something
basically wrong I'm doing about pasting your code. I have your
previous code working in one specific worksheet. It's now a matter of
making it universal. (In fact I don't see why the code has changed?)

And anyway, after downloading that DateFormat workbook, what is its
purpose? What am I supposed to do with it?

paste the code from the workbook. I guess you copied the code from a
website or a newsreader and you have issues with the word wrap.


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

Claus Busch wrote:

Hi Terry,

Am Thu, 25 Aug 2016 17:48:34 +0100 schrieb Terry Pinnell:

Thanks Claus but the issue is clearly more fundamental, something
basically wrong I'm doing about pasting your code. I have your
previous code working in one specific worksheet. It's now a matter of
making it universal. (In fact I don't see why the code has changed?)

And anyway, after downloading that DateFormat workbook, what is its
purpose? What am I supposed to do with it?

paste the code from the workbook. I guess you copied the code from a
website or a newsreader and you have issues with the word wrap.


Regards
Claus B.

The code now in Personal.xls pasted from the download is the same as
before, when I had pasted it as usual from my offline email/newsgroup
program (Agent).
And it's the same as the previous code pasted to the one workbook.

But if I enter dates in col C as before, the changes do not appear.

https://dl.dropboxusercontent.com/u/...cel-VBA-05.jpg

--
Terry, East Grinstead, UK

Customising date format
 

Hi Terry,

Am Thu, 25 Aug 2016 18:32:33 +0100 schrieb Terry Pinnell:

The code now in Personal.xls pasted from the download is the same as
before, when I had pasted it as usual from my offline email/newsgroup
program (Agent).
And it's the same as the previous code pasted to the one workbook.

But if I enter dates in col C as before, the changes do not appear.

https://dl.dropboxusercontent.com/u/...cel-VBA-05.jpg

the Worksheet_Change code must be in your workbook "SWCP 2016
Summary-Edit". In the PERSONAL you can paste the code from the standard
module.
Change in the 2. line Range("A:A") to Range("C:C")
The code only works in this spezific column.
Then select a cell with a date, press F2 and Enter.
If then the format doesn't change try:

Sub Events
Application.EnableEvents=True
End Sub
and try again.


Regards
Claus B.
--
Windows10
Office 2016

Customising date format
 

Oh no, I don't think Excel 365 supports custom menus/toolbars because
it was built specifically for online use. Sorry I did not catch that it
was this version!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Customising date format
 

GS wrote:

Oh no, I don't think Excel 365 supports custom menus/toolbars because
it was built specifically for online use. Sorry I did not catch that it
was this version!

Understood, no problem.

I subsequently installed the free 'Classic Menu' which adds this new
tab.

https://dl.dropboxusercontent.com/u/...ssicmenu-1.jpg

On clicking it I get something much more recognisable to me. But
having to open it for ANY command is annoying.

On your other point, are you saying that will be unable to customise
my toolbar as I could before in Excel 2000? If so, that's extremely
disappointing. I was just about to attempt doing so to get fast access
to the frequently used Format Painter. (Is there an assigned hotkey
for it that I've not yet found, BTW?)

Can't believe I'm paying £60 a year for this!

--
Terry, East Grinstead, UK

Customising date format
 

Claus Busch wrote:

Hi Terry,

Am Thu, 25 Aug 2016 18:32:33 +0100 schrieb Terry Pinnell:

The code now in Personal.xls pasted from the download is the same as
before, when I had pasted it as usual from my offline email/newsgroup
program (Agent).
And it's the same as the previous code pasted to the one workbook.

But if I enter dates in col C as before, the changes do not appear.

https://dl.dropboxusercontent.com/u/...cel-VBA-05.jpg

the Worksheet_Change code must be in your workbook "SWCP 2016
Summary-Edit". In the PERSONAL you can paste the code from the standard
module.
Change in the 2. line Range("A:A") to Range("C:C")
The code only works in this spezific column.
Then select a cell with a date, press F2 and Enter.
If then the format doesn't change try:

Sub Events
Application.EnableEvents=True
End Sub
and try again.


Regards
Claus B.

Thanks Clause. Been away fro a while. I'll have another try at this
soon.

--
Terry, East Grinstead, UK

Customising date format
 

Terry Pinnell wrote:

I was just about to attempt doing so to get fast access
to the frequently used Format Painter. (Is there an assigned hotkey
for it that I've not yet found, BTW?)

OK, sorted that point. Found this reasonably fast sequence of KB
shortcuts:

1. In source cell(s) Press Ctrl+C.
2. Select target cell(s) Press Shift+F10, S, R.

--
Terry, East Grinstead, UK