Active Cell stored as a variable

[richardson]

Hi All -

Is there a way to store the location of the active cell and then after some calculations are done in different cells the macro would bring you back to the cell where you started and not the cell where information was changed. BTW the incoming active cell will be different every time.

Other background:
I have a macro that is using worksheet_change to drive it. So after every calculation the active cell is drive back to the one that the macro is correcting. This is annoying to my users because the form is two pages long and the macro points to a cell on the first page.

Thanks for any help.

 

[Norie]

How is the active cell being moved/changed in the first place?

Are you selecting/activating?

If you are then you probably don't need to, and therefore don't need to move back to the active cell because you've never moved from it.

Can you post your code?

 

[richardson]

Code -dropdown based on another dropdown selection

Here is the code. Basically using the worksheet_change the user selects a dropdown from a list in e12 and then this macro runs and tells it what list to look at in i17. However this runs after every time a 'calculation' is done by excel. I have tried worksheet_selectionchange but when I use that I lose the ability to make a selection in my first dropdown.

(In case it isn't obvious, my codes are always cobbled and not elegant, hopefully you can swim your way through it.)



Private Sub worksheet_Change(ByVal Target As range)
If (range("e12").Value) = "APU" Then
range("i17").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
range("i17").Value = "blah blah blah"
ElseIf (range("e12").Value) = "ECS" Then
range("i17").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$D$1:$D$2"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ElseIf (range("e12").Value) = "EPS" Then
range("i17").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$D$2:$D$3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If

End Sub

 

[lenze]

You need to isolate your target. What cells do you wnat to trigger the code? E12?. If so, use

If Target.Address <> "$E$12" Then Exit Sub
Also, use Select Case Target instead of If's

lenze

 

[Norie]

Is the code only meant to be triggered if E12 is changed?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$12" Then
        Select Case Target.Value
            Case "APU"
                With Range("i17").Validation
                    .Delete
                    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
                    :=xlBetween
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
            Case Is = "ECS"
                With Range("i17")
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$D$1:$D$2"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
            Case "EPS"
                With Range("i17")
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$D$2:$D$3"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
        End Select
    End If

End Sub

 

[richardson]

Thanks. That worked. Does target.address mean activecell? Is it just a variable name for the active cell?

 

[lenze]

Thanks. That worked. Does target.address mean activecell? Is it just a variable name for the active cell?

Target is the name assigned to the cell CHANGED as a Range. It may or may not be the active cell. If you use the Selection_Change event, Target is the cell selected. Likewise, using the Before_DoubleClick event sets the target as the cell doubleclicked. Target has the same properties as any other range. Examples: Address, Count, Column, Row, Font, Style, Interior, Borders, Name

lenze