+ Reply to Thread
Results 1 to 2 of 2

Macro breaks in 2003

Hybrid View

  1. #1
    Registered User
    Join Date
    04-24-2006
    Posts
    14

    Macro breaks in 2003

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim Fig As String
    If ActiveSheet.ProtectContents = True Then
    LockAgain = True
    ActiveSheet.Unprotect Password:="hannah"
    End If
    Task = ""
    On Error GoTo finish
    Select Case Target.Address
    Case "$C$113:$D$113", "$C$116:$D$116"
    Fig = Target.Value(1, 1)
    Task = "Convert Text"
    Case "$F$113", "$F$116"
    Fig = Target.Value
    Task = "Convert Text"
    Case "$K$3", "$L$3", "$M$3"
        Select Case Target.Value
        Case Is <= 0
        If ActiveSheet.Range("$N$3").Value = 0 Then
            Task = "Grey Out Mortgages"
        End If
        Case Is > 0
            Task = "Goto Mortgage"
        End Select
    End Select
    Application.EnableEvents = False
    Select Case Task
    Case "Convert Text"
    Target = TextConvert(Fig)
    Case "Goto Mortgage"
        If ActiveSheet.Cells(80, 8).Value = "No Mortgage" Then
        ActiveSheet.Cells(80, 8) = ""
        End If
        If ActiveSheet.Cells(85, 4).Value = "No Mortgage" Then
        ActiveSheet.Cells(85, 4) = ""
        End If
        Application.Goto reference:=ActiveSheet.Cells(79, 1), Scroll:=True
        ActiveSheet.Cells(85, 4).Activate
    Case "Grey Out Mortgages"
        ActiveSheet.Cells(80, 8).Value = "No Mortgage"
        If ActiveSheet.Cells(85, 4).Value = "" Then
        ActiveSheet.Cells(85, 4).Value = "No Mortgage"
        End If
        If ActiveSheet.Cells(99, 8).Value = 0 Then
        ActiveSheet.Cells(87, 4).Value = 0
        ActiveSheet.Cells(89, 4).Value = 0
        ActiveSheet.Cells(91, 4).Value = 0
        End If
    End Select
    finish:
    If LockAgain = True Then
    
    ActiveSheet.Protect Password:="hannah"
    End If
    Application.EnableEvents = True
    End Sub
    I'm having some trouble with this Macro, basically, it works in Excel 97 but I'm having problems with it in excel 2003, it brekas everytime at Fig = Target.Value(1, 1)

    does anyone have any ideas?
    thanks!

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    what is the (1,1) of the comand
    Target.Value(1, 1)
    supposed to do?

    I have only ever seen code written as
    Target.Value
    or
    Target.Value.offset(1,1)
    . never as
    Target.Value(1,1)
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1