A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Ctrl+A macro fix for problem in Excel 2003



 
 
Thread Tools Display Modes
  #1  
Old June 17th, 2004, 06:25 PM
David McRitchie
external usenet poster
 
Posts: n/a
Default Ctrl+A macro fix for problem in Excel 2003

Ctrl+A macro fix for problem in Excel 2003
-------------------------------------------------------------

Restore normal Ctrl+A before risking your data. Ctrl+A has always
meant one thing (Select everything) in all PC applications that have
any selection or editing ability. Excel 2003 has deviated from this
standard.

The Excel developers decided that Excel 2003 would have
a double meaning for Ctrl+A there is only one other shortcut
that I know of with two meanings and that one was a toggle (Ctr+`).
Click Ctrl+A once and you get Ctrl+* (current region), click it
twice and you get what you expect (select everything: data & shapes).

The following macro uses application.RecordMacro to
generate the code that you would not otherwise see within a
recorded macro when a macro was invoked / embedded
(recording embedded macros was done prior to Excel 2000).

Naturally it is not the *best* solution because if you have macros
turned off in Excel 2003 you place your data at risk by not getting
what you expect.

Sub Ctrl_A()
'Excel 2003 Ctrl+A is FUBAR·ed in Excel 2003
' use this shortcut to cut your loses, (D.McR 2004-06-16)
' BEFORE you destroy your data integrity.
'Ctrl+A is fixed on this machine if assigned to Ctrl+A
'You must preserve the active cell or use of Ctrl+A
' for normal use such a preselecting a cell before Ctrl+A, then sort
Dim acell As Range
Set acell = ActiveCell
Cells.select
Application.RecordMacro "'Comment from Ctrl_A in " _
& ThisWorkbook.Name
Application.RecordMacro "Cells.Select ' Ctrl_A"
acell.Activate
Application.RecordMacro "Range(""" & acell.Address(0, 0) _
& """).activate ' Ctrl_A"
Beep 'if you want to indicate restored usage
End Sub

The above have been included included on an Excel 2003 topic on my
Shortcut Keys in Excel
http://www.mvps.org/dmcritchie/excel...x2k.htm#foobar

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm



  #2  
Old June 18th, 2004, 03:26 AM
Dave Peterson
external usenet poster
 
Posts: n/a
Default Ctrl+A macro fix for problem in Excel 2003

Maybe just assigning the macro to an .onkey procedu

Option Explicit
Sub auto_open()
Application.OnKey "^a", "myCtrlA"
End Sub
Sub auto_close()
Application.OnKey "^a"
End Sub
Sub myCtrlA()
Dim myCell As Range
Set myCell = ActiveCell
ActiveSheet.Cells.Select
myCell.Activate
End Sub




David McRitchie wrote:

Ctrl+A macro fix for problem in Excel 2003
-------------------------------------------------------------

Restore normal Ctrl+A before risking your data. Ctrl+A has always
meant one thing (Select everything) in all PC applications that have
any selection or editing ability. Excel 2003 has deviated from this
standard.

The Excel developers decided that Excel 2003 would have
a double meaning for Ctrl+A there is only one other shortcut
that I know of with two meanings and that one was a toggle (Ctr+`).
Click Ctrl+A once and you get Ctrl+* (current region), click it
twice and you get what you expect (select everything: data & shapes).

The following macro uses application.RecordMacro to
generate the code that you would not otherwise see within a
recorded macro when a macro was invoked / embedded
(recording embedded macros was done prior to Excel 2000).

Naturally it is not the *best* solution because if you have macros
turned off in Excel 2003 you place your data at risk by not getting
what you expect.

Sub Ctrl_A()
'Excel 2003 Ctrl+A is FUBAR·ed in Excel 2003
' use this shortcut to cut your loses, (D.McR 2004-06-16)
' BEFORE you destroy your data integrity.
'Ctrl+A is fixed on this machine if assigned to Ctrl+A
'You must preserve the active cell or use of Ctrl+A
' for normal use such a preselecting a cell before Ctrl+A, then sort
Dim acell As Range
Set acell = ActiveCell
Cells.select
Application.RecordMacro "'Comment from Ctrl_A in " _
& ThisWorkbook.Name
Application.RecordMacro "Cells.Select ' Ctrl_A"
acell.Activate
Application.RecordMacro "Range(""" & acell.Address(0, 0) _
& """).activate ' Ctrl_A"
Beep 'if you want to indicate restored usage
End Sub

The above have been included included on an Excel 2003 topic on my
Shortcut Keys in Excel
http://www.mvps.org/dmcritchie/excel...x2k.htm#foobar

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


--

Dave Peterson

  #3  
Old June 18th, 2004, 03:47 AM
David McRitchie
external usenet poster
 
Posts: n/a
Default Ctrl+A macro fix for problem in Excel 2003

Hi Dave,
The reason for the largeness of the macro is for macro recording
to record what the macro did, plus the comments.

Is there a particular advantage of using of onkey over assigning
a shortcut to the macro. I have in the back of mind, don't know why,
that onkey was something to avoid unless you had to use it.
Is the advantage of onkey that it would not be in the toolbars file,
ease of use, or speed?
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave Peterson" wrote in message ...
Maybe just assigning the macro to an .onkey procedu

Option Explicit
Sub auto_open()
Application.OnKey "^a", "myCtrlA"
End Sub
Sub auto_close()
Application.OnKey "^a"
End Sub
Sub myCtrlA()
Dim myCell As Range
Set myCell = ActiveCell
ActiveSheet.Cells.Select
myCell.Activate
End Sub


David McRitchie wrote:
Restore normal Ctrl+A before risking your data. Ctrl+A has always
meant one thing (Select everything) in all PC applications that have
any selection or editing ability. Excel 2003 has deviated from this
standard.
[clipped......]
The above have been included included on an Excel 2003 topic on my
Shortcut Keys in Excel
http://www.mvps.org/dmcritchie/excel...x2k.htm#foobar



  #4  
Old June 19th, 2004, 12:17 AM
Dave Peterson
external usenet poster
 
Posts: n/a
Default Ctrl+A macro fix for problem in Excel 2003

No reason (that I know). I guess I just find it easier sometimes to put it in
code than to explain how to do it via the user interface.

David McRitchie wrote:

Hi Dave,
The reason for the largeness of the macro is for macro recording
to record what the macro did, plus the comments.

Is there a particular advantage of using of onkey over assigning
a shortcut to the macro. I have in the back of mind, don't know why,
that onkey was something to avoid unless you had to use it.
Is the advantage of onkey that it would not be in the toolbars file,
ease of use, or speed?
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave Peterson" wrote in message ...
Maybe just assigning the macro to an .onkey procedu

Option Explicit
Sub auto_open()
Application.OnKey "^a", "myCtrlA"
End Sub
Sub auto_close()
Application.OnKey "^a"
End Sub
Sub myCtrlA()
Dim myCell As Range
Set myCell = ActiveCell
ActiveSheet.Cells.Select
myCell.Activate
End Sub


David McRitchie wrote:
Restore normal Ctrl+A before risking your data. Ctrl+A has always
meant one thing (Select everything) in all PC applications that have
any selection or editing ability. Excel 2003 has deviated from this
standard.
[clipped......]
The above have been included included on an Excel 2003 topic on my
Shortcut Keys in Excel
http://www.mvps.org/dmcritchie/excel...x2k.htm#foobar


--

Dave Peterson

  #5  
Old June 19th, 2004, 12:01 PM
David McRitchie
external usenet poster
 
Posts: n/a
Default Ctrl+A macro fix for problem in Excel 2003

Thanks, never would have guessed that aspect. I know you still
had to use oncode for keys you can't use in the user interface.

"Dave Peterson" wrote in message ...
No reason (that I know). I guess I just find it easier sometimes to put it in
code than to explain how to do it via the user interface.

David McRitchie wrote:
Is there a particular advantage of using of onkey over assigning
a shortcut to the macro.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.