Posts by Justin Doward

    Hi Jason,


    I cannot see how the sheets relate, ID 100000 for example disappears in sheet 3, ID 1040000 repeats on sheet 3 except it has brackets around the negative value and no account is taken of the 1459 that is present on sheet 2. ID 2309D is only on sheet 2 and nowhere else.


    Essentially I do not see what is supposed to be occurring.

    Hello UA,


    Try the attached, the code is as below:


    Note that I have made the list an excel table, it is not really necessary just how I have been doing things lately. Just use insert table and highlight your table of interest.

    There is an error thrown that I have not worked out, but the resume next corrects it for the list provided, it may be quite slow on your large database so try it on a few hundred/thousand lines first and see how it goes.

    Copy of SampleDataMediaList.xlsm

    Justin

    Hi Splat,


    Roy's point is that there is no scenario in which coding to select the cell will be more efficient then just writing to or taking information from the cell directly. Selecting ranges tends to be used by early coders before they learn how to navigate things properly, I believe Roy is trying to determine the overall intent of your coding so he can point you in the right direction but if you are happy that the system does what you are after then stick with it for now, you will probably correct it in the future.


    NB: you can easily load a full excel file here and get a working example returned to you where appropriate, i generally would not download and open a zip file.


    Glad my earlier post helped!


    Cheers

    Justin

    Hi MA,


    The code you have provided does not really do anything like what you are requesting, is there a reason you are using a combination of system popup and msgbox rather than just using msgbox?


    Do you want a message to be displayed when a cell changes or do you want a macro to run through the ranges and popup/msgbox on all values?

    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:


    Code
    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
    UserForm1.Show
    End If
    Exit_Sub:
    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.

    JD

    You can replace the code in the workbook_SheetChange event with:


    Code
    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.

    Justin

    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:


    change

    rCl.Interior.ColorIndex = lCol

    to

    rCl.DisplayFormat.Interior.ColorIndex = lCol


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


    HTH

    Justin

    Hi,


    Try this code in the sheet you posted.



    Let me know if it does what you are after.

    Justin

    Try this code in the code for the sheet:


    Code
    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.


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

    Justin

    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:


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

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