Posts by Justin Doward

    Hi DJ,

    Your scenario would be a lot easier to "imagine" if you provided an example sheet. In what you have written you are suggesting a column full of names, in column A but then you want to hide and show column A. What is happening to the names here? Do you just want a list of names, each name can have any number of lines inserted between one name and the next depending on a selected team size?

    Whatever the scenario an example sheet will save a lot of time.

    Hi Mikey,

    This will work on the example you have posted, itassumes the same format is always true.

    1. Function GetName3(target As String)
    2. x = InStr(1, target, Chr(10))
    3. If Left(target, 4) = "Dear" Then
    4. GetName3 = Trim(Mid(target, 5, x - 5))
    5. Else
    6. GetName3 = Trim(Mid(target, 1, x - 1))
    7. End If
    8. End Function

    HI KD

    I think this is what you were trying to do.

    Excel Template Macro V10 JDEDIT.xlsm

    Let me know how it goes.

    I was not sure why you were creating the sheets, then naming them using the array, and then renaming them in the same workbook open event, so I deleted that step... just change the array to name them correctly the first time.

    I also moved the array to the sheet so it might be easier to change the sheetnames etc, but you can run it from your original array just fine if you delete the list on the sheet.

    There were a few other confusions in there, but I think most of what you are after is there.


    You need to/could try working through the sheets without selecting them, just perform the operation on the range there is no need to select/activate either the sheet or the range.

    something like:

    1. Dim sh As Worksheet
    2. For Each sh In Sheets
    3. sh.Rows("2:18").Group
    4. sh.Rows("20").Group
    5. sh.Outline.ShowLevels rowlevels:=1
    6. Next
    7. End Sub

    Then write the code for the activewindows when they are being viewed.

    Hi AM,

    I ran the code on the sheets you uploaded, with using the RUN button on the host sheet and assigning this macro to the button and all sheets open. It works fine in this context.

    You appear to be running from a form is it?

    If the context has changed I need more information to correct the error.

    Did you try running the code in the context you uploaded your files?

    Hi AM,

    Try this method:


    You can try this code:

    I have it working in this workbook, you need to create a directory on your C drive called "c:\test" just press the button on the template sheet:


    I do not know how the onedrive directory works, but if this code is doing some of what you want I can have a look working out the directory.

    Hi Victor,

    The code below currently returns to the text box and highlights the text so you can either start typing to overtype the existing search or make a new selection using the same search. If you want to delete the text in the text box delete the .selstart and .sellength lines and remove the comment symbol to so the .value line works instead.

    I also put the option of a beep sounding rather than the msgbox appearing, again delete the beep line if that is not what you want.

    Then there is the option for a second textbox hidden below the listbox, if you expand the size of the form in developer and put a second textbox in there then shrink the form so it is hidden again the code will change the height (see attachment).

    We probably need to move from this thread, it is getting far from the original question. If you have a share drive for the file you could send me a link to the drive. Use the messaging rather than this thread.

    V10 JDedit2.xlsm

    Hi Victor, I had a look in context and understand how it works now, nice job!

    The alternative is to catch the error prior to exiting the form so the user has the chance to correct the selection without having to double click again, eg:

    Hi Victor,

    I am not sure what you are trying to do in your code for the doubleclick, there is no waythe mergearea should activate. It will only enter the if statement if you doubleclick on the range and then it will show the form, there is no reason for an error to occur so the error number will never not be 0 (unless there is something I am missing).

    If you want to discuss what you are trying to get to happen here PM me, if it is working as you expect all good.


    Hi Victor,

    I think this is all you need, otherwise I will need to clarify with you.

    This now catches the error if there is an error with the worksheet name, but ignores it and ends the sub if the error is due to a match fail.