Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Append Change event code ? (Excel 2002/2003)

    Hi

    I need to add these two change event codes into one but I keep getting block if without end if errors.



    Any help would be appreciated.

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Change event code ? (Excel 2002/2003)

    The problem is that the second of the procedures is already incorrect: almost all the Ifs miss the corresponding End If. Instead of all those If .. Thens, use Select Case - this makes the code easier to read.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C28"), Target) Is Nothing Then
    Me.CommandButton3.Visible = Not (Range("C28") = "Renewed")
    Me.CommandButton4.Visible = Not (Range("C28") = "New")
    End If
    If Not Intersect(Range("Market_Sector"), Target) Is Nothing Then
    Select Case Range("Market_Sector")
    Case "Processed_Food_94", "Agriculture_17T", "PCT_92", "Dairy_86", "Beverages_74"
    Worksheets("Agreement").Visible = xlSheetVeryHidden
    Case Else
    Worksheets("Agreement").Visible = xlSheetVisible
    End Select
    End If
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Append Change event code ? (Excel 2002/2003)

    Hi Hans

    Thanks for the prompt repy, thank you for the code and the explanation of same, whilst this works fine for me, it has thrown up another small problem.

    The Agreement sheet is on a navigitor menu as follows.

    Private Sub CommandButton17_Click()
    Sheets("Agreement").Select
    Unload Navigationfrm
    End Sub

    Is it possible to add an error code to this so that when the agreement sheet is hidden it will show a message box ?

    Many Thanks Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Append Change event code ? (Excel 2002/2003)

    Hi Hans

    I solved it myself with

    Private Sub CommandButton17_Click()
    On Error GoTo errhandler
    Sheets("Agreement").Select
    Unload Navigationfrm
    errhandler: Err.Clear
    End Sub

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Change event code ? (Excel 2002/2003)

    If you still want to show a message, you can use

    Private Sub CommandButton17_Click()
    If Sheets("Agreement").Visible = xlSheetVisible Then
    Sheets("Agreement").Select
    Else
    MsgBox "Agreement sheet is hidden.", vbInformation
    End If
    Unload Navigationfrm
    End Sub

    If you only want to unload the form if the sheet is visible, move the line Unload Navigationfrm up to above Else.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Append Change event code ? (Excel 2002/2003)

    Hi Hans

    Thanks that's a better solution.


    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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