Posts by Justin Doward

    Hi Mamun,

    You can try the approach in the attachment: Book1.xlsm

    I am not sure if you know how to navigate the code?

    there is code on the worksheet:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("G9:G1200")) Is Nothing Then
    If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then GoTo Exit_Sub
    End If
    End Sub

    Modify the range G9:G1200 as required.

    the this code attached to the userform:

    Just create your list of terms, insert it as a table (with header) and name it then change ws = sheet1 to refer to whichever sheet your table is one, and the Table1 to be whatever you name your table and it should work in whatever context you need.

    Let me know how it goes.


    You can replace the code in the workbook_SheetChange event with:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
    If Not Intersect(Sh.Range(target.Address), Sh.Range("C7:U41")) Is Nothing Then
    Application.ScreenUpdating = False
    Call background_color_count
    Application.ScreenUpdating = True
    End If
    End Sub

    Hi Hampton,

    I got this to work:

    Place a workbook_SheetChange event in the workbook code sheet.

    Then the sub in a module.

    I am sure there must be a more efficient way to check if iNts is empty but I kept getting a global method fail.

    Let me know how it goes.


    The function would be a better solution, rather than having to run the macro every time. Either that or writing the code into the sheetchange event so it updates whenever the conditional format is activated. But if the macro does what you need all is good :)

    Hi Roy, Hampton,

    I did not test the function but I expect it will not count cells that have been formatted using conditional format, to modify the function to count these cells insert DisplayFormat whenever referencing the colorindex, eg:


    rCl.Interior.ColorIndex = lCol


    rCl.DisplayFormat.Interior.ColorIndex = lCol

    Without the displayformat the code only counts cells that have had the background color changed using fill-color.




    Try this code in the sheet you posted.

    Let me know if it does what you are after.


    Try this code in the code for the sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3:BJ67")) Is Nothing Then
    If Range("BR" & Target.Row) > 116 Then
    MsgBox "over"
    ActiveSheet.Unprotect Password:="PW"
    Range("B" & Target.Row & ":BJ" & Target.Row).Locked = True
    ActiveSheet.Protect Password:="PW"
    End If
    End If
    End Sub

    let me know if it does what you are after.

    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.

    Function GetName3(target As String)
    x = InStr(1, target, Chr(10))
    If Left(target, 4) = "Dear" Then
    GetName3 = Trim(Mid(target, 5, x - 5))
    GetName3 = Trim(Mid(target, 1, x - 1))
    End If
    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:

    Dim sh As Worksheet
    For Each sh In Sheets
    sh.Outline.ShowLevels rowlevels:=1
    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.