Posts by mumphis

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    given a range named xyz which refers to d1:e3


    where the 3rd row is blank.

    I want to examine the row after the range with


    I find that this code refers to the last row of the range which is blank.

    How can I be sure that I will be 1 row (offset=1) below the range?

    I find the first iteration of this loop (counter=0) works fine. Subsequent iterations work EXCEPT the .MergeCells. Is there a better way to approach this?

    The .font and .value behave correctly.

    Re: Proper Case Text By Condition

    Thanks for all the replies, cheecky or otherwise. The data is supplied to me, I can not control the source(s).

    I will use a macro to handle the situation.

    .... and Stewie will Never take over the world (unless he learns Excel and VBA)....

    How can I extend proper() to NOT change "PO Box 333" to "Po Box 333". Ideally, I would like to supply a list of words such as PO and all the 2 letter directionals (NE,NW,SE,SW).

    There are also cases such as a last name of MacNamara which should have a capital M and N. Even worse, I see that 3rd becomes 3Rd which is very sad.

    I'm assuming the data was supplied in uppercase

    Many thanks.

    I would like to hear about products or methodologies related to your development environment. For instance, is there a 'compare' or 'diff' available so that I can compare my current VBA module (local to my machine)to the same module which is currently in production.

    How are larger projects managed when there are multiple developers? Is there any way to 'check out' a module from the production environment so that others are locked out of changing it while I develop code in the module?

    I am looping through all of the bookmarks in a document. There is a Excel name which is the same as the bookmark. I would like to retain the Excel format.

    numBookmarks = ActiveDocument.Bookmarks.Count
        For counter = 1 To numBookmarks
            bmk = ActiveDocument.Bookmarks(counter).Name
            Selection.GoTo What:=wdGoToBookmark, Name:=bmk
            Selection.TypeText Format(myWB.Sheets("Job Sheet").Range(bmk), "00000")   
        Next counter

    In the example, I have hardcoded the "00000" format for a U.S. Zipcode. Other defined names could be for dates, dollar amounts, etc.

    Is there a way to use the format found in Excel for that defined name.

    I have an array as follows in the form of 'lastname number date'

    lastname - could be one or two names
    number - could be 4 or 5 digits
    date - YYYYMMDD


    Jones 1234 20061010
    Smith 1255 20061105
    Robinson 1288 20030404
    Frederick Stevenson 1222 20040808

    I would like to sort by the number or date. There is always a blank in between the 3 pieces of data. I thought of using split to get the data, then bubble sort it and force the other pieces to follow along as they bubble up or down. It seems cumbersome.

    what would you suggest?

    When running this on Excel 2003, I receive a:
    Can't find Project or Library error message.

    It had been working on Excel 2000.

    Worksheets("W").OLEObjects(lbN).Object.AddItem  Format((Range(N).Value(1, 1)), "H:MM AM/PM")

    Would anyone know what library I need to reference or is there a better way to format it for the listbox?

    Many Thanks,

    Re: Activex Listbox - Assigning The Selected Row

    Thanks for the 2 replies. I figured it out. My error was that I had a ActiveX listbox control and a defined name that had the same name (they weren't called 'listbox1' in my code :)

    never would have thought there would be a conflict....

    My ActiveX listbox looks like:


    After loading the listbox via .additem, I want to set one of the rows as 'selected'. I have been trying with variations of the following without success.

    Worksheets("J1").OLEObjects("listbox1").Object.Selected(Counter) = True

    What is the correct syntax? I am using Excel 2000.

    I have a .txt file created in Notepad and it contains a 4 digit number (Job ID). I am recieving the dreaded "Bad File Mode" (#54) error when writing back the file.
    I would like to OPEN it as R/W locked, read it, increment it by 1 and write it back. Am perfectly willing to create the .txt as non-binary if that is causing the issue. Any suggestions would be greatly appreciated.

    Dim jinChar As String
        fileNumber = FreeFile()
        jinFP = "JIN.txt"
        Open jinFP For Binary Access Read Write Lock Read Write As #fileNumber
        Input #fileNumber, jinChar
        jobIDNumber = CInt(jinChar)
        jobIDNumber = jobIDNumber + 1
        jinChar = CStr(jobIDNumber)
        Write #1, jinChar    
        Close #fileNumber

    Re: File Locking A Flat File Over The Network

    to answer both Dave and Sicarii,

    We use Excel 2003 and the file size is tiny. It literally stores 1 number which represents the next available 'Job ID number' which is a 6 digit number. The goal would be to: lock, read, increment the job id, write it back. All clients would make their lock attempt in a loop, i.e. attempt to lock, if fail, wait a second, try again.

    There has been no corruption yet as I have not even coded this yet. I posed the question because I had heard that applications 'in general' can have corruption issues with RW over a network. Maybe its more of an issue if an app wanted to maintain an open RW connection for an extended period of time? If this is something the Excel/VBA community rarely or never sees, then all the better.

    I am not a networking expert but do know that their network is newly installed.

    Thanks to Dave and Sicarii for jumping in...

    Re: File Locking A Flat File Over The Network

    I was concerned about corruption over the network. If it is a non-issue, all the better. Only 1 client should be able to:
    lock, read, increment the job id, write it back at a time.


    I have a simple flat file that stores a 'next Job ID number' through VBA. It is stored on a network drive which can be accessed by clients in different physical locations.

    Are there any known issues with outside clients locking the file as 'Lock Read Write'. Is this prone to data corruption? Is there a set of steps that should be followed to keep the file from being corrupted?