ActiveCell and Worksheet_SelectionChange

[EssKayKay]

I’m still (or should I say again) working on my Amortization worksheet. I have one other problem. I’m hoping if I can resolve this it will point me in the right direction but we’ll see.
Simply as a test, I have the following code in my Private Sub Worksheet_SelectionChange(ByVal Target As Range) routine.

If ActiveCell.Offset(0, 0) <= 0 Then
    MsgBox "Empty"
Else
    MsgBox "Populated"
End If

However, I want the active cell to only be in the Range “M32:M400”. Is this possible and if so, any suggestions would be appreciated.
I’m sure I will be back with more questions but for now this should do.


Thanks,
Steve K.

 

[CephasOz]

Does this do what you want?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim strMsg As String
    If (Target.Column = 13) Then
        If ((Target.Row >= 32) And (Target.Row <= 400)) Then
            strMsg = "Empty"
            If Application.WorksheetFunction.IsNumber(ActiveCell.Value) Then
                If (ActiveCell.Value > 0) Then
                    strMsg = "Populated"
                End If
            End If
            MsgBox strMsg, vbInformation + vbOKOnly, "Value Check"
        End If
    End If
End Sub

 

[EssKayKay]

Does this do what you want?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim strMsg As String
    If (Target.Column = 13) Then
        If ((Target.Row >= 32) And (Target.Row <= 400)) Then
            strMsg = "Empty"
            If Application.WorksheetFunction.IsNumber(ActiveCell.Value) Then
                If (ActiveCell.Value > 0) Then
                    strMsg = "Populated"
                End If
            End If
            MsgBox strMsg, vbInformation + vbOKOnly, "Value Check"
        End If
    End If
End Sub

Thank you CephasOz. This did not do exactly what I wanted (probably because I didn't really know what I wanted) but you sent me in the right direction.
I changed the TargetColumn to a If Not Intersect(Target, Range("M33:M400")) Is Nothing Then
I have it working better now. I will still have to play with this a bit but it looks promising.

Again, much appreciated,
Steve K.

 

[EssKayKay]

I’m still working on my Amortize worksheet. My latest issue is I’m seeing an Error 1004: Application-defined or object-defined error.

I do not know what is triggering this nor particularly what it means. I have uploaded a test file for your review called Try Amortize (rev2).xls to Dropbox (Try Amortize (rev2).xls)

If you wish to assist, and see the error –

1. Download Amortize(rev2).xls from Dropbox. File name Try Amortize (rev2).xls
2. Open the file
3. Click on Paym’t. Made button (upper right corner)
4. This will direct you to the next available Payment Date opening. Enter a date.
5. You will see the 1004 Error.

I wish I could upload my file directly but apparently MrExcel does not allow that. Therefore I hope all works with Dropbox. If you have a problem, let me know.

 

[StephenCrump]

For me, your code errors on this line, because ActiveCell is A1. Your code has several occurrences of Range("A1").Select so it's not immediately obvious which previous line of code has caused the problem.

If ActiveCell.Offset(-1, 0) <= 0 Then
   If MsgBox("                    --- WARNING ---" _
       & vbNewLine & vbNewLine & "                Do not skip a Payment." & vbNewLine & _

It's rarely necessary to use .Select, .Selection, .ActiveCell etc. It's inefficient, and susceptible to errors like this - where the ActiveCell is not where you think it is.

'For example, instead of
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"

'Use
Range("A1").Value = 1

Cutting down on the use of .Select should also allow you to reduce the toggling of Application.EnableEvents.

I notice that at the moment, Sub Formulas2Values() appears to call Sub Worksheet_SelectionChange about 8 times, which I'm sure is not the intention.

 

[EssKayKay]

For me, your code errors on this line, because ActiveCell is A1. Your code has several occurrences of Range("A1").Select so it's not immediately obvious which previous line of code has caused the problem.

If ActiveCell.Offset(-1, 0) <= 0 Then
   If MsgBox("                    --- WARNING ---" _
       & vbNewLine & vbNewLine & "                Do not skip a Payment." & vbNewLine & _

It's rarely necessary to use .Select, .Selection, .ActiveCell etc. It's inefficient, and susceptible to errors like this - where the ActiveCell is not where you think it is.

'For example, instead of
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"

'Use
Range("A1").Value = 1

Cutting down on the use of .Select should also allow you to reduce the toggling of Application.EnableEvents.

I notice that at the moment, Sub Formulas2Values() appears to call Sub Worksheet_SelectionChange about 8 times, which I'm sure is not the intention.

Thank you very much Stephen. That appears to have done it. However, I now ran into another issue. I have much more testing to do. I'll keep you posted.

Again, much appreciated. . .