Select active cell after Save

[Jaye Cavallo]

Hello,

If possible, I would like to solve the following scenario by selecting the active cell after save:

1. I have a workbook with two sheets (sheet1 and sheet2), each protected so the user cannot change the formatting.
2. The before Save event triggers formatting procedures on both sheets, basically finding the last row and last column and then do some formatting on the cells that have data. This is because the user has the ability to paste content (text) into sheet1 or sheet2, and i can't account for every paste method to match source of destination.
3. Is there a way to select the last active cell, on either sheet1 or sheet2, when the Save is complete?

I am sorry in advance if my question is not clear.

 

[Anthony47]

If I correctly got your need:
-on starting your BeforeSave macro, save the current active cell:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim CACell As Range
On Error Resume Next
    Set CACell = ActiveCell
On Error GoTo 0
'continue your macro
'

-then just before ending, reset the position:

'
'your macro
If Not CACell Is Nothing Then
    Application.Goto CACell
End If
End Sub

Try...

 

[Jaye Cavallo]

Thank you. Your code achieved what I asked for and wanted. However, I am struggling with my code. For example, if I perform a Save when on sheet1, the last selected cell on sheet1 is indeed the last active cell, as expected. However, when I activate sheet2 after the Save, the entire range is still selected (example A1 to lastrow/lastcol). This is also the case if I save when sheet2 is the active worksheet.

This is the code I am using to select the range, then code to format the cells follows.

With Worksheets("sheet1_Name")
Dim rgFull As Range
Set rgFull = .Range(.Cells(1, 1), .Cells(lastrow, lastColumn))
End With

rgFull.Select

What do you suggest I do with the variable rgFull to deselect after formatting is complete? I would have to do the same for the sheet2 range variable.

 

[Anthony47]

I didn't get the problem...
In general, if you don't like having a certain area selected you will select another area.
For example, rgFull.Cells(1,1).Select will select the first cell of your rgFull (thus, given the way rgFull is calculated, it is equivalent to Range("A1").Select)


Btw, your code works only if the "selected sheet" is the same you insert in the With statement, so playing with With Worksheets /End With is useless, as by default "Range" and "Cells" refer to the currently selected sheet

 

[smozgur]

Your code achieved what I asked for and wanted.

Marked solution has been switched accordingly.

 

[Jaye Cavallo]

Sorry, for not responding sooner. Your code is very help. However, I guess I should explain the issue I am encountering now using the SelectionChange event instead of the BeforeSave event. I am trying to format the used cells/rows when a user presses enter on a given worksheet, in this case, the "Customer_Interactions" worksheet. When I insert the code you provided, before and after the formatting macro, Excel gets hung up and then then runs into an automation error where the object loses communication with object. If I don't insert your code, I end up in an endless loop of selecting all of the used cells.

This is the code I have for the customer_interactions selectionchange event:

Application.ScreenUpdating = False

'Format Customer Interactions Worksheet
Worksheets("Customer_Interactions").Activate

Worksheets("Customer_Interactions").Cells.Borders.LineStyle = xlNone

Dim lastrow As Long, lastColumn As Variant
lastrow = _
Worksheets("Customer_Interactions").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
lastColumn = _
Worksheets("Customer_Interactions").Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

' Get the range
With Worksheets("Customer_Interactions")
Dim rgFull As Range
Set rgFull = .Range(.Cells(1, 1), .Cells(lastrow, lastColumn))
End With

rgFull.Select
Selection.Font.Name = "Calibri"
Selection.Font.Size = 11
Selection.WrapText = True
Selection.EntireRow.AutoFit
Selection.VerticalAlignment = xlVAlignCenter
Selection.HorizontalAlignment = xlLeft

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Selection.Borders.Color = vbBlack
Selection.Locked = False
' With Selection.Interior
' .ColorIndex = xlNone
' End With

Worksheets("Customer_Interactions").Activate
Application.ScreenUpdating = True

 

[Anthony47]

It is not clear to me why you switched from a rare event (BeforeSave) to a continuously triggered event (SelectionChange); if you whish to start the macro immediately when someone type some information in the "Customer_Interactions" sheet then you should rather use the Worksheet_Change event.

I think it's useless commenting or patching your code: if you explain under which condition you would like to execute all those formatting (namely: which is the range of cells that when modified need to trigger the macro) than we will show you how to use the worksheet_change event

 

[Jaye Cavallo]

Ok. I was focused on the code and not on when I wanted when I wanted the macro to run. Thus, I did not realize I selected the wrong event for what I wanted to accomplish. I moved my code to worksheet change event seems to be running as you indicated. Thanks for correcting my error.

 

[Anthony47]

Good to know that you found the way to get out of the problem

BUT let me go with some comments...
You loaded the WorksheetChange code into the "Customer_Interactions" vba module (whereas the Workbook_BeforeSave code was in the ThisWorkbook vba module); and get triggered when you modify something on this worksheet (that thus is already selected)

All the code in this module by default refers to the sheet "Customer_Interactions"; so there is no any reason for selecting / activating that sheet, or for using With Worksheets("Customer_Interactions") /End With.
If you want to be esplicit, using "Me" would be ok
For example Worksheets("Customer_Interactions").Cells.Borders.LineStyle = xlNone OR Cells.Borders.LineStyle = xlNone OR Me.Cells.Borders.LineStyle = xlNone would be equivalent

But, most relevant, in most of the cases there is no need to "Select" an area to modify it.
For example, rather then

rgFull.Select
Selection.Font.Name = "Calibri"
Selection.Font.Size = 11
'etc etc

You might better use

'rgFull.Select             'Removed instruction
rgFull.Font.Name = "Calibri"
rgFull.Font.Size = 11
'etc etc

So going to your question

Is there a way to select the last active cell [when the macro] is complete?

With this approach, your question doesn't even need to be asked, because your macro didn't modify neither the selection nor the active cell