Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Intersect Target (XP; SP3)

    Can someone take a look at this code. For the most part I can not get it to work; although there are a few times it does.

    What I can trying to achieve is to look at each cell in Range("D11:I11") and determine if a file exists when entered in.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    On Error Resume Next
    If Not Intersect(Target, Range("D11:I11")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("D11:I11")).Cells
    var1 = oCell
    Var2 = Range("R_Path")

    TheFile = "" & Var2 & "" & var1 & ""
    Results = Dir$(TheFile)
    If Results = "" Then
    MsgBox "File Does Not Exist!"
    Else
    ' MsgBox "File does Exist!"
    End If

    Next oCell

    Application.EnableEvents = True
    End If
    End Sub


    John

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

    Re: Intersect Target (XP; SP3)

    The "" bits in the concatenation don't have any function, they can be omitted.

    What does the named range R_Path contain?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect Target (XP; SP3)

    The path to where the files should exist ie "C:FileLocation"

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Intersect Target (XP; SP3)

    You are going to have less trouble with these if you use Option Explicit, and Declare and Type all your variables. See if these changes help:

    If Right(Var2, 1) <> "" Then Var2 = Var2 & ""
    TheFile = CStr(Var2 & var1)

    Code runs fine with these and when I Declare and Type all variables. But it seems to be a bit of a guessing game to ask users to enter names until they get them right - wouldn't it be easier to get the file names from the selected path using

    Application.GetOpenFilename
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Intersect Target (XP; SP3)

    If I set R_Path to a valid path, the code works consistently for me.
    In what way doesn't it work for you - does it not detect that a file exists, or that a file doesn't exist, or do you get an error message?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect Target (XP; SP3)

    Can you tell us what does not work? Do you get an error message? The code below works for me:

    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim TheFile As String, Results As String
    On Error Resume Next
    If Not Intersect(Target, Range("D11:I11")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("D11:I11")).Cells
    TheFile = Range("R_Path").Value
    If Right(TheFile, 1) <> "" Then
    TheFile = TheFile & ""
    End If
    TheFile = TheFile & oCell.Value
    Results = Dir$(TheFile)
    If Results = "" Then
    MsgBox "File Does Not Exist!"
    Else
    MsgBox "File does Exist!"
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub
    </code>
    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect Target (XP; SP3)

    Hans,

    When I enter in a wrong file name into one of the cells in Range(D11:I11") nothing happens. The result is as if the file exits. I tried entering bad file names (those I know that don't exist) and should receive the Msgbox. On occassion I have got it to work but this is not the norm.

    Regards,
    John

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect Target (XP; SP3)

    Your code has the MsgBox statement for the file does not exist commented out. That would make it look like nothing happened.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Intersect Target (XP; SP3)

    I'm not having any trouble with this condensed version:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim strPath As String, strCellVal As String

    strPath = Range("R_Dir").Value ' since it's constant, no point checking it within the loop
    If Right(strPath, 1) <> "" Then strPath = strPath & "" ' check trailing slash
    On Error Resume Next
    If Not Intersect(Target, Range("D11:I11")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("D11:I11")).Cells
    strCellVal = oCell.Value
    If Len(Dir$(strPath & strCellVal)) Then
    Debug.Print strPath & strCellVal ' debug print it if it's OK
    Else
    MsgBox "File Does Not Exist!" ' user message if not OK
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect Target (XP; SP3)

    Legare,

    I am aware that the line was remarked out. I tried your code and continue to get the same results: no message box at all even when I take away the rem mark.

    John

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Intersect Target (XP; SP3)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Are you sure? Looks like the other way round to me.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect Target (XP; SP3)

    Are you putting the code into the event module behind the sheet that you want it to work on? You can not put this code into a normal module. To put the code into the Event module, right click on the sheet tab for the sheet where you want it to operate. In the popup menu, click on View Code. Paste the code here.
    Legare Coleman

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect Target (XP; SP3)

    You are correct, I was looking at it cross eyed.
    Legare Coleman

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect Target (XP; SP3)

    I rebooted my computer and wouldn't you know it.... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Now I wonder why the memory issue.

    Thanks for everyone's assistance.
    John

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Intersect Target (XP; SP3)

    I've done that. How far across the room did yours go? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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