return to the same cell and previous sheet

MILTINO

New Member
Joined
Jan 30, 2022
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
In a book with several sheets from any of them when I want to see a hidden sheet "Authorship" I call it through a macro
class=y2iqfc>In this Sheet "Authorship" I can be the time I give for later when I no longer need to be in this sheet exit and hide this sheet "Authorship" and return to the previous sheet in the same cell from which I went to the Sheet Authorshiplang=EN-US style='font-size:11.0pt;font-family:Arial;mso-ansi-language:EN-US'>
I send a macro this works with the particularity that it always sends me to the first visible sheetlang=EN-US style='font-size:11.0pt;font-family:Arial;mso-ansi-language:EN-US'>
Sub MUESTRA() ‘tecla ctrl+d
Application.ScreenUpdating = False '
Sheets("Autoria").Visible = xlSheetVisible
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
Application.DisplayFormulaBar = False
End With
Sheets("Autoria").Select
End Sub

Sub OCULTA()
Application.ScreenUpdating = False
Sheets("Autoria").Visible = xlSheetVeryHidden
With ActiveWindow
.DisplayHeadings = True
.DisplayWorkbookTabs = True
Application.DisplayFormulaBar = True
End With
On Error GoTo error:
error:
End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
My initial reaction is that you need to store the previous address somewhere either on a sheet on in a Named Range but this might work for you.

Rich (BB code):
Public gReturnToRng As Range

Sub MUESTRA() ' tecla ctrl+d
    Application.ScreenUpdating = False '
    
    Set gReturnToRng = ActiveCell
    Sheets("Autoria").Visible = xlSheetVisible
    With ActiveWindow
        .DisplayHeadings = False
        .DisplayWorkbookTabs = False
        Application.DisplayFormulaBar = False
    End With
    Sheets("Autoria").Select
End Sub

Sub OCULTA()
    Application.ScreenUpdating = False
    Sheets("Autoria").Visible = xlSheetVeryHidden
    With ActiveWindow
        .DisplayHeadings = True
        .DisplayWorkbookTabs = True
        Application.DisplayFormulaBar = True
    End With
    
    If Not gReturnToRng Is Nothing Then Application.Goto gReturnToRng
    On Error GoTo error:
error:
End Sub
	
	
	
	
	
	


Rich (BB code):
 
Upvote 0
Greetings, it gives me an error when I return
If Not gReturnToRng Is Nothing Then
 
Upvote 0
The only way I seem to be able to reproduce that error is to run the first macro (MUESTRA) a second time with the active sheet being "Autoria".

Try in the first macro (MUESTRA) making this replacement.
Rich (BB code):
Set gReturnToRng = ActiveCell
If ActiveCell.Parent.Name <> "Autoria" Then Set gReturnToRng = ActiveCell
 
Upvote 0
Parece que la única forma en que puedo reproducir ese error es ejecutar la primera macro (MUESTRA) por segunda vez con la hoja activa como "Autoria".

Prueba en la primera macro (MUESTRA) haciendo este reemplazo.
[CÓDIGO=rico]
Establecer gReturnToRng = ActiveCell
Si ActiveCell.Parent.Name <> "Autoria", entonces establezca gReturnToRng = ActiveCell
[/CÓDIGO]
gracias excelente solucion
Te veo pronto
 
Upvote 0
Thanks for letting me know. Glad I could help.
Funnily enough, that's not how it looked when I sent it to you ;)
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top