none
Menu to navigate through sheets doesn't work proper RRS feed

  • Question

  • I have created a menu to navigate through sheets. However, when I use one of the buttons to go to another sheet and type something in this another sheet the things that I type appear in the first one. Although when I use the tabs to jump to one sheet to another, it works fine. Seems to be that the macro is considering a relative reference instead an absolute one.

    Here is what my macro does (or at least should):

    Option Explicit

     

    Private Sub fechar_menu_Click()

        Menu.Hide

    End Sub

    Private Sub novo_pedido_Click()

        goToTab ActiveWorkbook.Worksheets("Novo pedido"), "B5"

    End Sub

    Private Sub ver_pedidos_Click()

        goToTab ActiveWorkbook.Worksheets("Consultar pedido"), "F1"

    End Sub

    Private Sub cadastrar_clientes_Click()

        goToTab ActiveWorkbook.Worksheets("Clientes")

    End Sub

    Private Sub cadastrar_produtos_Click()

        goToTab ActiveWorkbook.Worksheets("Produtos")

    End Sub

    Private Sub cadastrar_transportadoras_Click()

        goToTab ActiveWorkbook.Worksheets("Transportadoras")

    End Sub

    Private Sub painel_financeiro_Click()

        goToTab ActiveWorkbook.Worksheets("Painel Financeiro"), "B3"

    End Sub

     

    Private Sub goToTab(ByRef ws As Worksheet, Optional cel As String = vbNullString)

        Menu.Hide

        Application.ScreenUpdating = False

        With ws

            .Activate

            If Len(cel) = 0 Then

                .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Select

            Else

                .Range(cel).Select

            End If

        End With

        Application.ScreenUpdating = True

    End Sub

     

    Notes:

    This code works. The only problem here happens when I type something.

    It does not happen when I use the tabs to navigate from one sheet to another.

    I have tried to use ".activate" instead of ".select".

    I have tried to use only "Sheets ("Clientes").Select"; even so, the bug occurs. To execute "Excel /unregserver" on the Windows CMD solved my problem once, but I could not do this again. It does not seems to work anymore.

    I did a menu like this one once, but I did not had these problems.

    Well, here is the file: bit.ly/1WrEgcu. You can start at the "Novo pedido" sheet. From there hit the "Menu" button and go to "Cadastrar clientes" using the button with the same name. Once you are there type something and you will see that the typed text appears in the "Novo pedido" sheet instead of in the "Clientes" sheet. The code that I posted up there is in a form called "menu".

    Here's a video so you can picture what's going on:"bit.ly/1h0JCeL".

    Well, I guess I know a lot more about the problem now. I've tested the workbook over various computers and came to the conclusion that the problem is within the Office 2013. I have not seem this issue when running the 2007 or 2010 version.

    When the workbook opens the following, code is executed:

    Private Sub Workbook_Open()

        Application.DisplayFullScreen = True

        Application.DisplayFormulaBar = False

        ActiveWindow.DisplayHeadings = False

        ActiveWindow.DisplayGridlines = False

        ActiveWindow.DisplayWorkbookTabs = False

    End Sub

     

    That is it. I hope that somebody could help me.

    Wednesday, September 30, 2015 1:25 PM

Answers

  • Have you set the Userform Modal property to False? I can re-produce the problem if the Modal property is set to True.

    Actually you should not be able to write on any sheet when the Modal property is True but for some reason when the selection is made via VBA code, it allows it but it writes to the wrong worksheet.


    Regards, OssieMac

    Monday, October 5, 2015 6:27 AM

All replies

  • Replace all of the code with the code below. I have used a UDF (User Defined Function) in lieu of the called sub.

    The UDF returns a range object instead of a string. Note that I have dimensioned the Function as a Range object. Range objects have inbuilt information that includes the workbook name, worksheet name and range and then simply using Application.GoTo will go to the worksheet and select the range on the worksheet.

    I could not fully test on your workbook without the Ribbons etc but I did test in a dummy workbook with a dummy UserForm.

    Private Sub fechar_menu_Click()
        Menu.Hide
    End Sub

    Private Sub novo_pedido_Click()
        Dim sht As Worksheet
        Set sht = ActiveWorkbook.Worksheets("Novo pedido")
        Application.Goto rngGoTo(sht, "B5")
    End Sub

    Private Sub ver_pedidos_Click()
        Dim sht As Worksheet
        Set sht = ActiveWorkbook.Worksheets("Consultar pedido")
        Application.Goto rngGoTo(sht, "F1")
    End Sub

    Private Sub cadastrar_clientes_Click()
        Dim sht As Worksheet
        Set sht = ActiveWorkbook.Worksheets("Clientes")
        Application.Goto rngGoTo(sht)
    End Sub

    Private Sub cadastrar_produtos_Click()
        Dim sht As Worksheet
        Set sht = ActiveWorkbook.Worksheets("Produtos")
        Application.Goto rngGoTo(sht)
    End Sub

    Private Sub cadastrar_transportadoras_Click()
        Dim sht As Worksheet
        Set sht = ActiveWorkbook.Worksheets("Transportadoras")
        Application.Goto rngGoTo(sht)
    End Sub

    Private Sub painel_financeiro_Click()
        Dim sht As Worksheet
        Set sht = ActiveWorkbook.Worksheets("Painel Financeiro")
        Application.Goto rngGoTo(sht, "B3")
    End Sub

    Function rngGoTo(ByRef ws As Worksheet, Optional cel As String = vbNullString) As Range
        Menu.Hide
        With ws
            If cel = vbNullString Then
                Set rngGoTo = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
            Else
                Set rngGoTo = .Range(cel)
            End If
        End With
    End Function


    Regards, OssieMac

    Thursday, October 1, 2015 6:23 AM
  • An after thought.

    If you would like a copy of the test workbook then there is a zipped copy at the following link.

    Also forgot to say previously that your problem could be more related to using a Modal Userform. If you want to edit the worksheet then set the Userform Modal property to False.

    http://1drv.ms/1N3hpCF


    Regards, OssieMac


    Thursday, October 1, 2015 6:38 AM
  • Hi Ezequias,

    Could you share us how you designed your sheet Clientes? I made a test with a new sheet, and it worked correctly. If I create a new sheet, and copy Clientes content to it, it worked correctly. But if I move or copy Clients, it reproduced your issue. I assume that it was caused by this specific sheet.

    For a workaround, I suggest you copy Clientss content to a new sheet.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, October 5, 2015 6:01 AM
  • Have you set the Userform Modal property to False? I can re-produce the problem if the Modal property is set to True.

    Actually you should not be able to write on any sheet when the Modal property is True but for some reason when the selection is made via VBA code, it allows it but it writes to the wrong worksheet.


    Regards, OssieMac

    Monday, October 5, 2015 6:27 AM
  • Hi OssieMac,

    Your suggestion worked great.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, October 5, 2015 8:08 AM