Consulting

Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast
Results 41 to 60 of 98

Thread: Solved: Search engine

  1. #41
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Okay, just to be difficult, I opted on an easier method than all the progress indicators. I only had a couple of minutes tonight, and it's way quicker to go this route. To use the progress indicator, the code should really be broken up a bit, so that it can be called in chunks from the userform. I say this because the trigger point to update the file count is in the middle of the block.

    I don't have the time to go that route, so I've settled on updating the StatusBar in the bottom left hand corner of the Excel screen instead. (Where it probably says "Ready" right now.) It still gives progress, but unfortunately isn't as noticeable as the full on progress indicators we talked about earlier. I'm still willing to give those a shot, but let's try this first and see if it works (and is acceptable) for you.

    I've also added two functions, which go in the standard module with the findall routine. One is to check if the directory exists, before jumping into the FindAll code, and the other is to count the number of files in the directory to measure our progress. One caveat about that one, though... it counts all files, so can give incorrect results if you have non-excel files. I had 4 files, 1 a zip file, so it ran up to 75% complete, and then finished. It didn't get to 100%, as the routine only actually opens Excel files. (The code still completes, it just looks like it's done prematurely.)

    So here's all the new code:

    STANDARD MODULE:
    [vba]Option Compare Text
    Option Explicit
    Function FolderExists(Folder As String) As Boolean
    'Function purpose: To count all files in a directory
    Dim fso As Object, _
    SubFolder As Object

    'Create objects to get a count of files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    Set SubFolder = fso.GetFolder(Folder).Files
    If Err.Number <> 0 Then
    FolderExists = False
    Else
    FolderExists = True
    End If
    On Error GoTo 0
    End Function
    Function CountFiles(Directory As String) As Double
    'Function purpose: To count all files in a directory
    Dim fso As Object, _
    SubFolder As Object

    'Create objects to get a count of files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    Set SubFolder = fso.GetFolder(Directory).Files
    If Err.Number <> 0 Then
    CountFiles = 0
    Else
    CountFiles = SubFolder.Count
    End If
    On Error GoTo 0
    End Function
    Sub FindAll(SearchPath As String, SearchText As String)
    'Macro purpose: Loop through all Excel Workbooks and search each of them for the
    'specified criteria
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FileName As String
    Dim TermFound As Boolean
    Dim i As Integer, x As Integer
    Dim Processed As Integer, ToProcess As Integer
    Dim BoxText As String
    Dim Problem As Boolean
    'Turn off screen flashing
    Application.ScreenUpdating = False

    'Count number of files to proces, and assign first filename in directory to
    'FileName variable
    ToProcess = CountFiles(SearchPath)
    FileName = Dir(SearchPath & "\*.xls", vbNormal)

    Do
    'Inform the user how many files have been processed
    Processed = Processed + 1
    Application.StatusBar = "Processing file " & Processed & " of " & _
    ToProcess & " (" & Int(Processed / ToProcess * 100) & "% complete)"

    'Set problem to false then attempt to open workbook and set WB & WS variables
    '(If an error results, set the Problem variable to true)
    Problem = False
    On Error Resume Next
    Set WB = Workbooks.Open(FileName:=SearchPath & "\" & FileName, ReadOnly:=True) _
    ', Password:="Uncomment and put password here if required"
    Set WS = WB.Sheets("Data")
    If Err.Number <> 0 Then Problem = True
    On Error Resume Next

    If Problem = True Then
    'If an error resulted, (Problem is True,) close the workbook
    '(On error resume next in case WB never opened at all)
    On Error Resume Next
    WB.Close False
    On Error GoTo 0

    Else
    'If no error, check all textboxes in the file for search term
    TermFound = False
    For i = 1 To WS.TextBoxes.Count
    BoxText = WS.TextBoxes(i).Text
    If InStr(1, BoxText, SearchText) > 0 Then
    'If the search term is found, set TermFound to true,
    'and stop checking further (exit the loop)
    TermFound = True
    Exit For
    End If
    Next i
    If TermFound = False Then
    'If the search term was not found, close the file
    WB.Close False
    Else
    'If the search term was found, add 1 to the count of
    'opened files using x as variable to hold the info
    x = x + 1
    End If
    End If
    'Release WB & WS variables and set FileName to next file
    Set WB = Nothing
    Set WS = Nothing
    FileName = Dir() 'sets FileName to next file in directory
    Loop Until FileName = ""

    'Inform the user how many files were opened (number of opend files held in x)
    MsgBox x & " files were found which matched your search term!", _
    vbOKOnly + vbInformation, x & " Files Found!"

    'Restore screen updating and clear statusbar
    Application.ScreenUpdating = True
    Application.StatusBar = False

    End Sub[/vba]

    USERFORM CODE:
    [vba]Option Explicit
    Private Sub cmdOkay_Click()
    Const MainPath = "\\Disc1\StockData\Year"
    Dim Prompt As String
    'Make sure both textboxes have values assigned
    If Not IsDate(tbDate.Value) Then Prompt = "Please enter a prompt date" & vbCrLf
    If tbSearch.Value = "" Then Prompt = Prompt & "Please enter something to search for"
    If Prompt = "" Then
    'If Prompt is empty, then no problems were detected
    If FolderExists(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm")) Then
    'If the folder for the month exists call the FindAll routine
    Call FindAll(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm"), tbSearch.Value)
    Unload Me
    Else
    'If the folder for the month does not exist, notify the user
    MsgBox "The information you entered generated a file path of:" & vbCrLf & _
    MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm") & vbCrLf & vbCrLf & _
    "That file path does not exist! Please modify your selection and try again!", _
    vbOKOnly + vbCritical, "Folder does not exist!"
    End If
    Else
    'If Prompt is not empty, tell the user what info need correcting and return to the
    'userform
    MsgBox "Sorry, but I need more information!" & vbCrLf & Prompt, _
    vbCritical + vbOKOnly, "Hang on!"
    End If
    End Sub
    Private Sub cmdCancel_Click()
    'Unload the userform
    Unload Me
    End Sub
    Private Sub UserForm_Initialize()
    'Put today's date in the userform
    tbDate.Value = Format(Now(), "mm/dd/yyyy")
    End Sub[/vba]

    Let me know how that works, and if you'd rather go the full progress indicator.

    Cheers,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  2. #42
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Great Ken, thanks. It works. You are right that it's not as noticeable as the other bars but it works fine for me (if you can find a way though to get one of the cool bars in the routine I won't say no ).

    The error messages are great. It tells you the route where you went wrong. I've tried to create errors and I now only get an error when the map it searches in is empty. The rest of the errors are covered.

    The other thing is, the search is always in the "Data" sheet. Today I got the idea that it would also be nice if I could search the "stock" sheet which is in the same workbook. In here we have two textboxes too. I've tried [VBA] Set WB = Workbooks.Open(FileName:=SearchPath & "\" & FileName, ReadOnly:=True) _
    ', Password:="Uncomment and put password here if required"
    Set WS = WB.Sheets("Data")
    Set WS = WB.Sheets("Stock")
    If Err.Number <> 0 Then Problem = True
    On Error Resume Next[/VBA]
    But that's not to smart because it then only searches through the "Stock" sheets.

    Thanks again for all the trouble,

    Regards.

  3. #43
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Airborne,

    I'll try to look at the progress bar again tonight. I have been thinking on it, and it may not be a difficult as I thought. Give me a bit more time to mull it over.

    As for the rest:
    I've tried to create errors and I now only get an error when the map it searches in is empty.
    This should take care of that error. Replace all the code in the cmdOkay_Click routine in the userform, (after the dim prompt statement) with this:
    [vba]'Make sure both textboxes have values assigned
    If Not IsDate(tbDate.Value) Then Prompt = "Please enter a prompt date" & vbCrLf
    If tbSearch.Value = "" Then Prompt = Prompt & "Please enter something to search for"
    If Prompt = "" Then
    'If Prompt is empty, then no problems were detected
    If FolderExists(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm")) Then
    If CountFiles(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm")) > 0 Then
    'If folder for the month exists & has files in it, call FindAll routine
    Call FindAll(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm"), _
    tbSearch.Value)
    Unload Me
    Else
    'If no files are in the directory, inform the user
    MsgBox "The information you entered generated a file path of:" & _
    vbCrLf & MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm") & _
    vbCrLf & vbCrLf & "There are no files in that directory!" & vbCrLf & _
    "Please modify your selection and try again!", _
    vbOKOnly + vbCritical, "Directory is empty!"
    End If
    Else
    'If the folder for the month does not exist, notify the user
    MsgBox "The information you entered generated a file path of:" & vbCrLf & _
    MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm") & vbCrLf & vbCrLf & _
    "That file path does not exist! Please modify your selection and try again!", _
    vbOKOnly + vbCritical, "Folder does not exist!"
    End If
    Else
    'If Prompt is not empty, tell the user what info need correcting and return to the
    'userform
    MsgBox "Sorry, but I need more information!" & vbCrLf & Prompt, _
    vbCritical + vbOKOnly, "Hang on!"
    End If[/vba]

    The other thing is, the search is always in the "Data" sheet. Today I got the idea that it would also be nice if I could search the "stock" sheet which is in the same workbook. In here we have two textboxes too.
    I've changed the FindAll routine so avoid setting WS to a specific worksheet, and instead set it up to loop through all worksheets in the workbook. It will exit (leaving the workbook open) the first time it finds a match in a textbox on any sheet.

    To do this, gong both the set WS lines in the following:
    [vba]Set WB = Workbooks.Open(FileName:=SearchPath & "\" & FileName, ReadOnly:=True) _
    ', Password:="Uncomment and put password here if required"
    '\\\delete this line! Set WS = WB.Sheets("Data")
    '\\\delete this line! Set WS = WB.Sheets("Stock")
    If Err.Number <> 0 Then Problem = True
    On Error Resume Next[/vba]


    And wrap the contents of the Else section of the Do Loop with the For Each WS loop:
    [vba] For Each WS In WB.Worksheets
    'If no error, check all textboxes in the file for search term
    TermFound = False
    For i = 1 To WS.TextBoxes.Count
    BoxText = WS.TextBoxes(i).Text
    If InStr(1, BoxText, SearchText) > 0 Then
    'If the search term is found, set TermFound to true,
    'and stop checking further (exit the loop)
    TermFound = True
    Exit For
    End If
    Next i
    If TermFound = False Then
    'If the search term was not found, close the file
    WB.Close False
    Else
    'If the search term was found, add 1 to the count of
    'opened files using x as variable to hold the info
    x = x + 1
    Exit For
    End If
    Next WS[/vba]

    Hope that all makes sense, but post back if not. (Or if you think of anything else! )

    Cheers,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  4. #44
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hello Ken, I get the message "Ambigous name detected:FolderExists" in line "If FolderExists(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm")) Then". It pops up right after I click [OK] on the form (date and text to search for is filled in).


    Regards.

  5. #45
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Doh!

    If memory servers, that usually happens because you have two routines with exactly the same name.

    Can you check your workbook to see if the routine exists twice (maybe in two separate modules). If not, try any other open workbooks.

    Let me know,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  6. #46
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    sorry, sorry Ken. I was to quick to reply and you were to quick to answer. I kept the old code in the workbook and put your modifications in an new module but forgot to change the names, double names!

    I've changed the names of the "old" module. I've tried to make all the errors and it's realy fantastic how they are handled in your modified routine. GREAT!

    Thanks and regards.

  7. #47
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Shoot!

    Just found out that VBA has a native FolderExists method. Nothing like re-inventing the wheel!

    I don't think that would be the issue though, as we've been using that code for a bit, and it hasn't errored out. In addition, I went into the file I've been using and commented out the entire FolderExists function I wrote, and the code won't run. (Not calling the native version correctly.)

    I'm pretty sure you must have a second copy of the function kicking around in your WB somewhere.

    Having said that, I'll try and look at that code tonight as well. There's no point in using a User Defined Function if a native one exists, as the native ones are always faster.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  8. #48
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by airborne
    I was to quick to reply and you were to quick to answer
    Looks like there might be another case of that just happened from my side!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  9. #49
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Ken, I've tried the routine by puting some text in the textbox of the stock sheet but it doesn't see any text. The Data sheet is always the first sheet you see when the workbook opens. It finds everything I ask for in the data sheet but it ignores the stock sheet.
    And sorry that I was not clear enough but in the monthly maps are also some timesheets with only the sheet named Time in them but I don't want the routine to open those files. I guess you must be getting a bit tired of my replies .

    Regards.

  10. #50
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi there,

    It shouldn't matter if the Data sheet is first, or not, as the code is set up to go through each sheet in the file, then each textbox on the sheet.

    I need to ask a couple of stupid questions...
    -Are you certain that the textboxes were set up exactly the same as the other ones?
    -You're sure the search term is in the box on the Stock sheet (sorry, I had to ask!)
    -Can you upload a new copy of a target file that has textboxes you're using on both pages? The one I downloaded doesn't have any textboxes on the Stock page. (Pull out any confidential data first though.)

    With regards to the timesheet files, they would actually have been opened with the old macro, then closed immediately when it couldn't find the Data sheet. This version will take slightly longer as it looks through the sheet for textboxes, and then closes it. Is there a standard naming convention to the time sheet files? If there is, we can prevent it from opening them at all.

    And not at all tired, by the way! If you can stay patient, I'm enjoying helping out. It hasn't been that long since I was in the same boat!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  11. #51
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hi Ken. I'm very patient. I learn a bit of VBA every day and my workbook gets better every day. What more do I want .


    About your questions:
    I need to ask a couple of stupid questions...
    -Are you certain that the textboxes were set up exactly the same as the other ones?
    Yes, I'll send you a new copy.

    -You're sure the search term is in the box on the Stock sheet (sorry, I had to ask!)
    Yes I put some comments in it but it wasn't found by the search routine.

    -Can you upload a new copy of a target file that has textboxes you're using on both pages? The one I downloaded doesn't have any textboxes on the Stock page. (Pull out any confidential data first though.)
    See att.

    And you are right, there are no textboxes in the timesheet so it doesn't matter.

    Thanks and regards.

  12. #52
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Sorry forgot the attachment

  13. #53
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    While you're looking at progress bars, check out Stephen Bullens WksPrgrs.zip at this link >> http://bmsltd.ie/Excel/SBXLPage.asp

  14. #54
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Thanks jonske. Not only nice to look for bars, the site is filled with great VBA stuff.

    Regards.

  15. #55
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Airborne,

    Sorry, got deadlines today, and ran into systems issues at home last night. I'll try to get back this afternoon if I can free up some time....

    EDIT...

    Never mind. Took a quick look and saw the issue.

    Try this out:

    [vba]Sub FindAll(SearchPath As String, SearchText As String)
    'Macro purpose: Loop through all Excel Workbooks and search each of them for the
    'specified criteria
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FileName As String
    Dim TermFound As Boolean
    Dim i As Integer, x As Integer
    Dim Processed As Integer, ToProcess As Integer
    Dim BoxText As String
    Dim Problem As Boolean
    'Turn off screen flashing
    Application.ScreenUpdating = False

    'Count number of files to proces, and assign first filename in directory to
    'FileName variable
    ToProcess = CountFiles(SearchPath)
    FileName = Dir(SearchPath & "\*.xls", vbNormal)

    Do
    'Inform the user how many files have been processed
    Processed = Processed + 1
    Application.StatusBar = "Processing file " & Processed & " of " & _
    ToProcess & " (" & Int(Processed / ToProcess * 100) & "% complete)"

    'Set problem to false then attempt to open workbook and set WB & WS variables
    '(If an error results, set the Problem variable to true)
    Problem = False
    On Error Resume Next
    Set WB = Workbooks.Open(FileName:=SearchPath & "\" & FileName, ReadOnly:=True) _
    ', Password:="Uncomment and put password here if required"
    If Err.Number <> 0 Then Problem = True
    On Error Resume Next

    If Problem = True Then
    'If an error resulted, (Problem is True,) close the workbook
    '(On error resume next in case WB never opened at all)
    On Error Resume Next
    WB.Close False
    On Error GoTo 0

    Else
    TermFound = False
    For Each WS In WB.Worksheets
    'If no error, check all textboxes in the file for search term
    For i = 1 To WS.TextBoxes.Count
    BoxText = WS.TextBoxes(i).Text
    If InStr(1, BoxText, SearchText) > 0 Then
    'If the search term is found, set TermFound to true,
    'and stop checking further (exit the loop)
    TermFound = True
    Exit For
    End If
    Next i
    If TermFound = True Then Exit For
    Next WS
    If TermFound = False Then
    'If the search term was not found, close the file
    WB.Close False
    Else
    'If the search term was found, add 1 to the count of
    'opened files using x as variable to hold the info
    x = x + 1
    End If
    End If
    'Release WB & WS variables and set FileName to next file
    Set WB = Nothing
    Set WS = Nothing
    FileName = Dir() 'sets FileName to next file in directory
    Loop Until FileName = ""

    'Inform the user how many files were opened (number of opend files held in x)
    MsgBox x & " files were found which matched your search term!", _
    vbOKOnly + vbInformation, x & " Files Found!"

    'Restore screen updating and clear statusbar
    Application.ScreenUpdating = True
    Application.StatusBar = False

    End Sub[/vba]

    Will look at rest of stuff later! Gotta run...

    Cheers,
    Last edited by Ken Puls; 11-04-2004 at 11:56 AM. Reason: Saw the issue...
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  16. #56
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hi Ken, even though you were in a hurry, you solved it so now it checks the text in both sheets. Great!

    It's getting almost perfect now. The only thing is that the screen keeps flickering. At first you could also see the files opening/closing in the taskbar but I added [VBA]Application.ShowWindowsInTaskbar = False[/VBA] and at the end [VBA]Application.ShowWindowsInTaskbar = True[/VBA] So the sheets with the hits can only be seen at the end of the routine.
    Now It would be great if the original sheet would stay in focus and that the opening/closing of the searched sheets would stay in the background. Don't know if that's possible. But now you see every checked sheet opening and closing. I've tried to put [VBA]Application.ScreenUpdating = False[/VBA], but in the end it was all over the routine and the flickering kept on going.

    I don't know if the next question should come in a new thread.......do you know a routine that gives a message when people click on the upper right [X] of Excel to tell them to close by clicking on a button in the sheet? So closing by clicking [X] is not possible in this workbook only.


    Thanks and regards.

  17. #57
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Hi Airborne
    Ken's done all the hard work so.....
    [VBA]
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = True
    MsgBox "Please close by using the button"
    End Sub
    [/VBA]

  18. #58
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Great mdmackillop, thanks for the quick reply. Works!

    Regards.

  19. #59
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    I like the easy bits!

  20. #60
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Sorry. I think I've done something wrong. I thought it worked because when I click on the button It doesn't close and I get a message to close using ....button. But it now is impossible to close the file.


    Regards.

Posting Permissions

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