Posts by vwankerl

can you post a sanitized version of your workbook so we could look at how it is functioning now?

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?

I suggest you search for help doing resource scheduling, such as class scheduling or room reservations. Another possibility would be Gannt charts or project tracking.

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
C.EntireRow.Delete
Else
'when information is found
MsgBox "Invoice already paid!"
End If
End If
Next C
End Sub
[/VBA]

When you say "open a spreadsheet" do you mean any spreadsheet in any workbook or any spreadsheet in the same workbook as the MasterLookup?

So, are you saying you have 1 workbook with 56 worksheets and 1 worksheet that gets updated from the 56 worksheets? That would me the 1 workbook needs to be shared with 56 or more users at the same time? This is for clarification of your question.

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.

I have a Table that I need to add rows to and then place data in a couple of the cells in the new row. I am able to create the new row but do not the syntax for referencing the cells by their column name rather than an index. Basically I want to use structured referencing in VBA like I can in formulas.

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: Macro for Conditional Formatting - Excel 2007 based on Date/Time Value

I will look at your sample workbook, but right now I have a question:

Is it necessary to use Conditional Formatting? Does the background color change during the week?

Re: Formula to recognise a new sheet being added to a file, then returning a value

How does a new sheet get added? Is it manually or is there some VBA involved? More than likely you can't do it with a formula unless you have a UDF. And even then I think you will have to catch an Event to know when a new sheet is added.

Re: Is there a reason that this macro does not jump to the next worksheet?

Try something:

put

Application.ScreenUpdating = True

as the last line in the first macro (before the end sub)

I want to be sure that you are not mistaking the fact that the screen doesn't change with the macro not stepping through the sheets.

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

CTW,

here is some code that I think will help you:

Sub test()
   Dim fn As Variant, txt As String, x As Variant, y As Variant, i As Long, n As Long, c As Long
   Dim myNum As String, myPool As String, t As Integer, txtTemp As String
   Dim Filters As String, FilterIndex As Long, Title As String
   Const delim As String = "|"
     
   Filters = "Text files, *.txt, All Files, *.*"
   FilterIndex = 1
   Title = "Select the file to import"
   fn = Application.GetOpenFilename(Filters, FilterIndex, Title)
   If fn = False Then
      Exit Sub
   End If
   txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
   x = Split(txt, vbLf)
   n = 2
   c = 0
   With CreateObject("Scripting.Dictionary")
      .CompareMode = 1
      Do Until Cells(n, 1).Value = ""
         myPool = Cells(n, 1).Value
         .Item(myPool) = n
         n = n + 1
      Loop
      n = n - 1
      
      For i = 0 To UBound(x)
         If x(i) <> "" Then
            myNum = Trim$(Split(x(i), delim)(1))
            myPool = Trim$(Split(x(i), delim)(0))
            If myNum = "01" Or myNum = "16" Then
               If Not .exists(myPool) Then
                  n = n + 1
                  Cells(n, 1).Value = myPool
                  .Item(myPool) = n
                  Cells(n, 1).Value = myPool
               End If
               If myNum = "01" Then
                  t = 2
               ElseIf myNum = "16" Then
                  n = Range("A:A").Find(myPool, , xlValues, xlWhole).Row
                  t = 35
                  Do Until t > 47 Or Cells(n, t).Value = ""
                     t = t + 3
                  Loop
               End If
               txtTemp = x(i)
               txtTemp = WorksheetFunction.Substitute(txtTemp, Chr$(13), "")
               y = Split(Split(txtTemp, delim, 3)(2), delim)
               Cells(n, t).Resize(, UBound(y) + 1).Value = y
            End If
         End If
      Next i
   End With
End Sub

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.

VWankerl

Re: Import and Flatten Delimited Record-Type Text File

CTW,

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.

vwankerl

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

grunwaldtx,

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.

vwankerl

Re: Unhide multiple sheets based on username

GMAGDNAA,

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.

vwankerl

Re: Import and Flatten Delimited Record-Type Text File

CTW,

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.

Vern

Re: how can i organize my macro which got told having a length too long by excel

Thanks for the information. I have not had occasion to have to work with Excel formulas for other countries (although I have helped some with questions from the UK and AU) so did not know about the ";" instead of ",". I will have to remember that if I need to help others outside the US.