Posts by gijsmo

    Re: Macro refuses to finish running against a file on the server

    Yep, the long way around is to export and delete all your existing code, save the file with no macro code whatsoever and then reopen the file and import all your code back again. It's worth a shot.

    Of course, I am not sure if code cleaner does anything else in the process....maybe you can take your code home and clean it there!

    Re: Macro refuses to finish running against a file on the server


    Often when I start to see "phantom" problems like this I use a free utility called VBA Code Cleaner to sort this out. Code Cleaner removes the accumulated "gunk" that gets into a project especially after continuous changes to the code. In many cases, it will shrink the file size quite a bit too.

    I am using it in Excel 2003 but it claims to work up to Excel 2007 (although it did fail to install on a Windows 7 64 bit machine that I tried it on). The link is here (I am not affiliated with this tool in any way):

    Re: Update old workbook with data from another workbook

    OK, there are a couple of ways to approach that, some more difficult than others. Given the code is using a Find method to locate the same URL in the destination sheet, you can trim the URL that you pass the Find method but the Destination sheet may still contain extra spaces in the URL.

    Option 1 - trim all the URLs in the Destination sheet before calling the Find method (this is cumbersome)
    Option 2 - use a partial match in the Find method and trim the URL you are looking for. This will work fine if each URL is unique in the destination sheet. Problems will occur otherwise eg, if you have 2 URLs in the destination sheet like and then when you search for the second URL you may inadvertently find the first one if you only do a partial match.

    Assuming your URLs are unique enough to avoid this partial match problem you could recode part of the GetLatestDates routine as follows:

    The two changes I made are highlighted in blue in the code.

    The other way to find the date value you are looking for on the destination sheet is using a VLOOKUP but that has similar problems if you have spaces you need to trim or if your URLs are not unique.

    Re: Update old workbook with data from another workbook

    Just to clarify - do you mean you do not want to copy the data from the destination sheet to the source sheet if the date field is "blank" (or empty) on the destination sheet?

    If so then just add an "If" test to the part of the code that pastes the value back to the source sheet (ie, check to see if Result is a date value):

    If Not rFound Is Nothing Then
              'if the URL was found on the destination sheet
              'grab the date that is 1 column to the right if it contains a value
              Result = rFound.Offset(ColumnOffset:=1).Value
             [I] [B]If IsDate(Result) Then[/B][/I]
                'paste the new date into column B on the source sheet
                wsSource.Cells(lNum, 2) = Result
              [I][B]End If[/B][/I]
            End If

    Re: Print hidden sheets without viewing the sheet being printed

    Maybe try adding this code before and after you print:

    This will stop the sheets being printed from being seen.

    I don't do enough automated printing to help with your other question but this may (or may not) be what you are after:

    Re: Move last x worksheets to a new workbook

    If you only need to move the "report" sheets then you could modify my code slightly to only move those sheets. This is in the SaveChangesToNewWorkbook routine:

    The "If" test has been added to see if the sheet name contains "- Report". If it does, it will be moved, otherwise it will be ignored.

    Of course, this bit of code won't work with my previous code as my "dummy" report names did not contain a hyphen before the report (they were "Report 1", "Report 2", etc). But that's easily rectified by changing my dummy report names to "1- Report", "2- Report", etc.

    Of course, if you only want to move the "report" sheets, then my code is probably overkill as it assumes that any sheet that was added needs to be moved.

    Re: Update old workbook with data from another workbook

    Based on several assumptions in the information you have provided and the Source and Destination files, the following bit of code should do the trick:

    Paste this into the Source file and run the GetLatestDates routine to update the dates from the Destination file. For the purposes of the demo, the Source and Destination files are assumed to be in the same folder. The Destination file does not need to be open when you run this code.

    Read the comments in the code to see what is going on but basically the codes processes each row of the Source file and tries to find the matching URL in the Destination file (the URLs are assumed to be unique as the code only searches for the first matching URL). If a matching URL is found in the destination file, it grabs the date from that file and pastes it into the Source file. Voila!

    Re: Non-activex Datepicker Calendar Control

    One of the reasons I keep reading this forum is that I never stop learning!

    In the back of my mind, I knew the duplicated code I had on Sheet1 and Sheet2 in the last DatePicker demo could be combined but I never really investigated it - mainly because it gave the programmer some scope to set a min and max date range for each sheet if required.

    However, having seen a response by mikerickson just today to a question on the Help forum, the lightbulb suddenly lit up. Of course! The code for all sheets in the workbook could be stored in the ThisWorkbook module. And any code that needs to modify the behaviour of the pop-up calendar in a particular sheet could be handled by the ProcessPopUp routine as it could be passed the sheet name.

    Whilst I was at it, I did some investigating of named "arrays" as I thought it would be easier to store a min/max date pair into an array, especially if multiple min/max date pairs were required. So, after more hunting on the ozgrid forums, a few more changes sorted this out as well. And, yes, although Excel does not make it easy to type in or modify a named constant or array, it means changes can be made outside of the macro code.

    So in the attached version, the code for Sheet1 and Sheet2 has been "moved" (with modifications) to the ThisWorkbook module and there's been a slight tweak to the SheetPop module to do with how the named constant POPUPTYPE was referenced.

    Re: Move last x worksheets to a new workbook

    I do not believe you can move more than one sheet at a time to a new workbook.

    And rather than thinking "statically" ie, move the (same) x worksheets to a new workbook, my approach would perhaps be to look at the worksheets before and after you allow reports to be created.

    The worksheets before anything changes could be compared to the worksheets after the reports are created and any sheet that didn't exist before could then be moved to a new workbook. By storing these in perhaps a global array, you won't need to worry about where exactly in the workbook the new sheets have been created.

    A demo simulating this approach is below:

    Just copy this code into a new module in a test spreadsheet somewhere and run the CreateAndMoveReports routine. Be warned that because we are also simulating no changes to the worksheet that contains the original data (and this macro code) it is best to save the new spreadsheet with this code in it before you run the demo.

    This is just a simulation. To be useful the code that creates the array of existing worksheets before anyone can make changes should probably be placed in a Workbook_Open procedure.

    Likewise if nothing is required to happen with the new reports until the user tries to close the workbook, the routine to create the array of worksheets after reports have been created and then move those sheets to a new workbook should probably be placed in a Workbook_BeforeClose or Workbook_BeforeSave procedure.

    Re: Word VBA .FileSearch Replacement attempt...

    Hi Joe,
    I believe both Word and Excel have the FullName property ie,
    ActiveDocument.FullName for Word
    ActiveWorkBook.FullName for Excel

    The FullName includes the path name which is what I think you are after. To extract just the path name out of the FullName property you could use something like:

    Function JustPathName(sFullPath As String) As String
      JustPathName = ""
      If InStrRev(sFullPath, "\") = 0 Then Exit Function
      JustPathName = Left(sFullPath, InStrRev(sFullPath, "\"))
      Exit Function
    End Function

    So, using your example you would use:
    Set sPath = JustPathName(ActiveDocument.FullName)

    Re: Non-activex Datepicker Calendar Control

    cytop, one question based on how this might be implemented:


    Have reworked it a little to position the form offset fromthe mouse pointer

    If this is meant to also work on the worksheet, what would happen if the activecell was moved using just the arrow keys and not the mouse?

    Or is the intention to only display the pop-up if the mouse is clicked on the activecell?

    Re: Resizing A Named Range...

    I think you are only missing a Set statement when you resize the range, try this:

    Public Sub redefine_foo()
      Dim r As Range
      Set r = ActiveWorkbook.Names("NAMED_RANGE_FOO").RefersToRange 
      [I]Set[/I] r = r.Resize(r.Rows.Count + 1, r.Columns.Count + 1)
      ActiveWorkbook.Names.Add "NAMED_RANGE_FOO", r
      Set r = Nothing
    End Sub

    Re: Changing form control properties - not at runtime

    Barb-B...try this bit of code:

    Note that this will require a reference to the Microsoft Extensibility Library, something like:
    Tools --> References --> Microsoft Visual Basic for Applications Extensibility x.xx

    Re: Non-activex Datepicker Calendar Control

    I'd love to see it.

    My current projects using the date picker only need to use the drop-down calendar on a UserForm. I incorporated Chip Pearson's Form Positioner into the demo just to show how the calendar could work relative to the active cell on the worksheet but it may be overkill if you have something that can achieve the same result in far fewer lines of code.

    Re: Lookup & open file based on partial filename

    I haven't had time to check the forum lately as I am contending with an "upgrade" to Windows 7 64 bit and Office 2010. I still don't have everything working but that's part of the fun of upgrading I suppose. Right now, I am back on my Windows 7 32 bit partition with good old faithful Excel 2003!

    It looks like you have everything you need now and are on the road to understanding more about VBA...hopefully also you are using my latest code as per my previous post!

    Anyhow, as I mentioned previously, I am grateful for the praise but I think you should get your management to buy you a decent (and easy to follow) book on VBA rather than compensating me for my time - I've learned from this too and I will tuck the information away for when I next need it.

    Re: Count Problem

    I am not sure if you are talking about an "array" in the sense of requiring an array formula but assuming you mean a range of cells (let's say A1:A10), try the following formula:

    Re: Non-activex Datepicker Calendar Control

    If you investigated the new code in Sheet1 and Sheet2, you may have noticed (aside from the fact it is identical) that I created a "shell" routine to deal with the calendar pop-up called ProcessPopUp.

    The idea was that each of the triggers for popping up the calendar should call this routine (left click, right click, double click, etc). That way, you could specify a min date, max date and whether the calendar starts on a Sunday all in one place for each worksheet.

    However, I missed updating the Worksheet_BeforeDoubleClick event code - it is still calling the HandleSheetPopup event directly instead. So, to use this correctly, the Sheet1 and Sheet2 code for this event should look like:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Cancel = True
      [I]ProcessPopUp[/I] Target
    End Sub 'Worksheet_BeforeDoubleClick

    I have attached a version with this correction and also showing an example of how you can use named variables to limit the date range of the pop-up calendar on a sheet. The Range tester sheet (Sheet2) has some code in the ProcessPopUp routine to do this.

    Of course, there are numerous ways to store the min and max date including global variables or dates read from a worksheet somewhere or even a calculated value...this is just one example of how to deal with it.

    Re: Test if a specific userform is loaded

    Try this bit of code to see if the form exists in the UserForm collection:

    This will not load the Form that you are trying to check.

    You will need to pass the UserForm Name as a string eg,
    If Not IsUserFormLoaded("Form_BBB") Then Load Form_BBB

    Re: Merge three spreadsheets with one common column but sometimes different info

    Of course, the caveat with my code example is that IDs are "unique". However, the Find method that is used in the code would probably be better changed to ensure that the correct ID is always found. At the moment the code looks something like:

    Set rFound = .Columns(1).Find(What:=Item, After:=.Cells(1, 1), LookIn:= _
                       xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
                       xlNext, MatchCase:=False, SearchFormat:=False)

    The LookAt parameter is currently set to xlPart. However, this means that if you have an ID of 12 and and ID of 120 it is possible that the code will find the ID of 120 when looking for an ID of 12 (depending on the order of the IDs).
    So it is probably best to change all the references to LookAt:=xlWhole instead.