Disable R1C1 Style Option To A1 Style

    Re: Disable Changing R1c1 Style To A1 Style


    Putting this in the ThisWorkbook code module should do it.

    Code
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.Calculation = xlR1C1
End Sub


    Does your spreadsheet require R1C1 to run properly?

    Re: Disable Changing R1c1 Style To A1 Style


    I'm not sure if it is required, but I have written a lot of VBA code using r1c1 (writing r1c1 functions into cells), and don't want to take the change of it getting messed up when someone tries to change the style back to a1.


    Thanks for your reply. I will try it out.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]This gives me a runtime error 1004 : method failed


    whenever I do anything in the workbook

    Re: Disable Changing R1c1 Style To A1 Style


    You can grey out Tools > options tab for the workbook. Place this code in ThisWorkbook and save the workbook. When the user opens it the option is greyed out and when they close the workbook the option is available again


    Code
    Private Sub Workbook_Open()
Dim Opt As Object
Dim TB As Object
  
    Set TB = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
    Set Opt = TB.Controls("Options...")
    Opt.Enabled = False


End Sub


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Opt As Object
Dim TB As Object
  
    Set TB = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
    Set Opt = TB.Controls("Options...")
    Opt.Enabled = True
    
End Sub


    VBA Noob

    Re: Disable Changing R1c1 Style To A1 Style


    Use this code in ThisWorkbook. It's a lot less brutal :)


    Code
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ReferenceStyle = xlR1C1
End Sub

    Re: Disable R1C1 Style Option To A1 Style


    I agree this seems less brutal than hiding the option menu, however if I try to copy and paste something, the clipboard empties while in this sub, before I can hit paste.


    I will have to stick with hiding the option menu unless there is a better way to accomplish this without bad side effects.


    Any ideas are welcome!


    Thanks

    Re: Disable R1C1 Style Option To A1 Style


    How about this in ThisWorkbook Module;

    Code
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ReferenceStyle = xlR1C1
End Sub


Private Sub Workbook_Open()
Application.ReferenceStyle = xlR1C1
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login