Posts by vwankerl

    The problem is going to be having multiple users updating the same workbook at the same time, even if they are working on separate worksheets. Does the MasterSheet have to be updated in real-time or can it be done like at end of day? I would start out approaching it as 56 separate workbooks (1 for each user) that have the same VBA. These would then update the Master workbook like a database. This would remove the frustration of having multiuser access/update of the same workbook; plus it would isolate the data of each user from the others. This is not a trivial exercise but I have done similar things so it is doable.

    When the MasterSheet is updated, does it have to pass information (like summary information) back to the users in real-time?

    Here is some code that might work:

    [VBA]Private Sub Worksheet_Change(ByVal Target As Range)
    Dim C As Range, cellFound As Range, cellSearch As Range
    If Intersect(Target, Me.Range("K:K")) Is Nothing Then Exit Sub
    For Each C In Intersect(Target, Me.Range("K:K")).Cells
    If C.Text = "p" Then
    Set cellSearch = Intersect(Target.EntireRow, Me.Range("I:I"))
    Set cellFound = Worksheets("Paid").Cells(Rows.Count, "I").EntireColumn.Find(cellSearch.Value)
    If cellFound Is Nothing Then
    C.EntireRow.Copy Worksheets("Paid").Cells(Rows.Count, "K").End(xlUp).Offset(1).EntireRow
    'when information is found
    MsgBox "Invoice already paid!"
    End If
    End If
    Next C
    End Sub

    I have developed a VBA program which is a custom userform with some custom controls (all in VBA) that works as a timeclock for employee punchin/punchout processing. It should be running 24/7. At the center of the program is a do-loop with DoEvents to allow a timer based on the settimer API to perform some processing (dashboard updating) while the actual form is idle. The program crashes everyday just before 7AM and will not function until after 7AM. I am using Excel 2016 but prior to yesterday I was using Excel 365. Change to the stand-alone Excel 2016 thinking the Office 365 updating was possibly causing a problem. That did not fix anything. I have tried to disablingany processing that should occur when the timer activates but that did not change anything. I can find nothing in the OS (Windows 10) or Excel or my code that is specific to a specific time like that.

    Re: Need VBA method to "bake" conditional formatting, ie make it permanent

    Your reply was of no help to this thread at all. My application that I was referring to needed to freeze the Conditional Format at a certain point in a second worksheet. Simply copying the worksheet only copied the Conditional Formatting so it could/would change undesirably if someone made a change to the second worksheet. I had to use VBA to freeze the Conditional Formatting to prevent those changes. The program works well in Excel 2003 but the major changes in 2007 and 2010 breaks this code. There seems to be no one who has worked out the same functionality for 2007 or 2010 yet.

    I understand that Conditional Formatting is primarily a UI tool, not a data analysis tool. But because it is a very good tool for UI, users will use it to help them analyze data. Once they have the analysis they are looking for they often need to lock that down in some way. But with Conditional Formatting that "lock down" is no easy to do. Just by saying we need to duplicate the formatting conditions in code to do the lock down does not solve the problem.

    Plus, Excel is used more for the UI than for data analysis.

    Again I ask "malathion" if they have had any success in their efforts to solve the problem they were posting about. If so there are many of us who would value any results they have.

    Re: Need VBA method to "bake" conditional formatting, ie make it permanent

    Have you had any success with this? All the code I have found to do this (and some of it I have used), only works with Excel up through 2003. Excel 2007 and 2010 introduced some major changes in the way Excel handles Conditional Formatting. My experience is that what worked in 2003 may work sometimes in 2007 or 2010 but then may not work at other times. I don't think we can count on older code to handle Conditional Formatting will work in the latest Excel versions.

    Re: Dictionary object returning keys before they are added

    I downloaded your sample workbook and tested it. It appears that the Add is treating the ProtoElement as two separate items with the same key instead of as 1 object. You might try to force ProtoElement to an actual array rather than a Variant but I don't know if that will do anything or not. I can't find any reference to what the Dict.Add method does with different item types. I will keep looking and maybe test a couple of ideas. I will let you know what I find.

    Re: Import and Flatten Delimited Record-Type Text File


    here is some code that I think will help you:

    You will note that I used the GetOpenFilename function to prompt the user for the file to import instead of hardcoding the path. This allows the user to pick up files that may be pretty much any where they can access.

    Second, I added a Do Loop to pre-load the Dictionary object with any Pool Numbers already in the worksheet. That way if you have a text file that you want to use to update an existing worksheet it will work properly. Also, the sample text file you provided had a record type 16 with no matching 01 type record. This code will create a record with only the type 16 data in it. This code also doesn't care if the type 16 comes before or after the type 01 so you do not need to be concerned with the sort of the text file.

    Hope this does it for you! Ask any questions! The unasked question never gets answered.


    Re: Import and Flatten Delimited Record-Type Text File


    I have been looking this over with your last question in mind. I have found that if the text file is not in a particular sorted order, you will not get the right result. Such data sensitive dependencies result in problems later on that are very difficult to solve. I am looking at jindon's code to see what needs to be done to remove those dependencies.


    Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    I have done quite a bit of work managing the User Interface for various Excel applications. I understand the need to do so with multiple users who are either computer illiterate or overly self-confident (to their own detriment). I will look at the code you have sent and see if I can help or give you some direction.


    Re: Unhide multiple sheets based on username


    A question to clarify, is the userid the same as the userid used to login to the workstation or if you are on a network, to login to the Active Directory? That is what I assumed from the code you posted in your original post.


    Re: Import and Flatten Delimited Record-Type Text File


    I completely understand what you are saying. I just wanted to make sure there were no unstated requirements that would cause a problem later. I will be giving this some thought and effort in the coming days. Nothing here that is all that difficult. I will keep you updated as I make progress.