Thanks David,
This new code makes me both happy and mad!
Happy because it works.
Mad because this was *exactly* the very first code that I wrote, and I do not understand why it did not work then and does now!
This is why I tried others unnecessarily long.
But the shortcut key CTRL w does not work.
I have been able use many Excel Shortcut key for my own macros (what do I need CTRL w to close the workbook when I have a nice X to the right of the window), like CRTL u and CTRL SHIFT U, without problem.
But this one does not seem to accept to leave it's place...
Any idea? I am running short of letters.
Thanks again, Louise
--- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...> wrote:
>
> The problem with this code is that the variable xReference is being tested
> to see if it is xlA1 and also tested later to see if it is xlR1C1. However,
> it is never set to either of those when it is created, so neither of the IF
> statements will be true.
>
> There are two ways to deal with this:
>
> Firstly, change the
>
> > End If
> > If xReference = xlR1C1 Then
>
> pair of lines to a single Else. This will ensure that one part of the code
> always gets executed.
>
> Alternatively (or additionally), remove xReference completely and simply do
> the IF test(s) against Application.ReferenceStyle.
>
> Note also that the "Exit Sub" statements are not needed.
>
> Code to try (but I haven't tested this)
>
> Sub Switch_Reference_Style()
> 'shortcut CTRL w
> If Application.ReferenceStyle = xlA1 Then
> Application.ReferenceStyle = xlR1C1
> Else
> Application.ReferenceStyle = xlA1
> End If
> End Sub
>
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "garilou" <garilou@...>
> To: <ExcelVBA@yahoogroups.com>
> Sent: Monday, January 02, 2012 7:13 AM
> Subject: [ExcelVBA] Macro to change reference style
>
>
> > Hi group!
> > First Happy new Year to all!
> >
> > Long time you did not read from me.
> > I try a long time (and Google a lot) before I turn to you, but this time I
> > do not find much.
> >
> > I am trying to quickly change the Excel formula style.
> >
> > Here is one of the many codes that I have tried.
> > This one is the longest: I am pretty sure there are lots of unnecessary
> > code in it.
> >
> > Public xReference As XlReferenceStyle
> > Sub Switch_Reference_Style()
> > 'shortcut CTRL w
> >
> >
> > If xReference = xlA1 Then
> > xReference = xlR1C1
> > Application.ReferenceStyle = xlR1C1
> >
> > Exit Sub
> > End If
> >
> > If xReference = xlR1C1 Then
> > xReference = xlA1
> > Application.ReferenceStyle = xlA1
> > Exit Sub
> > End If
> > End Sub
> >
> > Each of those lines, tried separately, work.
> > Application.ReferenceStyle = xlR1C1
> >
> > or
> >
> > Application.ReferenceStyle = xlA1
> >
> >
> > But the quick switch does not.
> >
> > Thanks for you help
> >
> > Louise
> >
> >
> >
> >
> >
> >
> > ------------------------------------
> >
> > ----------------------------------
> > Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> > here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> > the ARCHIVES for EXCEL VBA.
> >
> > ----------------------------------
> > Visit our ExcelVBA group home page for more info and support files:
> > http://groups.yahoo.com/group/ExcelVBA
> >
> > ----------------------------------
> > More free tutorials and resources available at:
> > http://www.mousetrax.com
> >
> > ----------------------------------Yahoo! Groups Links
> >
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 10.0.1416 / Virus Database: 2109/4115 - Release Date: 12/31/11
> >
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar