Dear Group
I have used this for a long time :-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '
' Toggle between R1C1 and A1 Reference Style
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '
Private Sub ToggleReferenceStyle()
With Application
If .ReferenceStyle = xlR1C1 Then
.ReferenceStyle = xlA1
Else
.ReferenceStyle = xlR1C1
End If
End With
End Sub
'Private' hides the subroutine from the macro list. Attach it to a button in the toolbar.
Regards
Derek +++
>________________________________
> From: David Smart <smartware.consulting@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Sunday, 1 January 2012, 20:25
>Subject: Re: [ExcelVBA] Macro to change reference style
>
>
>
>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@cgocable.ca>
>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
>>
>
>
>
>
>
[Non-text portions of this message have been removed]
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