User Profile
waygar
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Sharepoint Workbook reversion to earlier version due to Internet link broken VBA
A group of eight geographically separated people coauthor an Excel macro-enabled Workbook located on a Sharepoint Server. This system works well with editing by each of the group being autosaved to the Sharepoint workbook in realtime.The group are connected to the Sharepoint through VPN and all use the latest version of Microsoft 365 (Desktop Excel). When a VPN times out or when one of the group's laptop suffers an internet disconnect, the latest version of the Workbook can revert to an earlier version consistent with the version at VPN timeout or internet disconnect of that particular laptop when it comes back on line with Excel and the Workbook open I gather because the autosave refreshes the more modern online Workbook with that of the now aged version on the laptop in question even though other members of the group have not suffered any disconnect and continue to edit the Workbook. Is there a way within VBA to monitor for such a disconnect forcing the laptop in question to close its occurrence of Excel immediately before autosave does its damage when the internet re-connects?Solved894Views0likes3CommentsAllow Edit Ranges in a Protected Sheet - set and change permissions for Users using VBA
I have a (Microsoft 365) Excel workbook that is shared on Sharepoint amongst 8 users. The way I have set it up is that they all can edit a particular range in a worksheet that is protected (Allow Edit Ranges with normal users able to edit a designated range with no password whilst the rest of the worksheet is protected with a password) with a user or users who can edit anything in the worksheet without using a password - in effect a superuser or an administrator. I would like to be able to change the user permissions such that another user can be designated as an administrator (ie able to edit the worksheet without a password whilst keeping) etc. However, I have not found anywhere where I can actually do this using VBA. Currently I have to physically unprotect the sheet using a password and then change the permissions under the Allow Edit Ranges tab. Is there a way to change these permissions under the Allow Edit Ranges Tab using VBA? Thank you.821Views0likes0CommentsRe: Speeding Up VBA execution
Hi Hans, Is there a way, using VBA, to change the permissions for defined users such as they can, edit or not, the Allow Edit Ranges set in a Protected Sheet using VBA and not just change the permissions manually each time eg when a user (with limited editing permission) is changed from being just a user to an administrator (with full editing permission)?779Views0likes1CommentRe: Speeding Up VBA execution
A better and corrected version of the above is below and no faster" Sub Switch() Dim v5, v6, v9, v11 As Variant Dim NRows3 As Long Dim I As Long NRows3 = 25330 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Range("J6:J" & NRows3).Insert Shift:=xlToRight Range("I6:I" & NRows3).Insert Shift:=xlToRight v5 = Range("E6:E" & NRows3) v6 = Range("F6:F" & NRows3) v9 = Range("I6:I" & NRows3) v11 = Range("K6:K" & NRows3) v11 = v6 v9 = v5 Range("E6:E" & NRows3) = v5 Range("F6:F" & NRows3) = v6 Range("I6:I" & NRows3) = v9 Range("K6:K" & NRows3) = v11 'Range("F6:F" & NRows3).Copy Destination:=Range("K6:K" & NRows3) 'Range("E6:E" & NRows3).Copy Destination:=Range("I6:I" & NRows3) Range("F6:F" & NRows3).Delete Range("E6:E" & NRows3).Delete Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub2.2KViews0likes8CommentsRe: Speeding Up VBA execution
Thanks Hans, Firstly my testing using the code below does not seem to speed up the original code unless you have a better approach and secondly all the formatting associated with array processing is lost unfortunately so another technique rather than array processing would be necessary? Your thought? Cheers, Wayne Sub Switch() Dim v3 As Variant Dim NRows3 As Long Dim I As Long NRows3 = 25330 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Range("J6:J" & NRows3).Insert Shift:=xlToRight Range("I6:I" & NRows3).Insert Shift:=xlToRight v3 = Range("A7", "Q" & NRows3) For I = 1 To NRows3 - 6 v3(I, 11) = v3(I, 6) v3(I, 9) = v3(I, 5) Next I 'Range("F6:F" & NRows3).Copy Destination:=wshCOMPL.Range("K6:K" & NRows3) 'Range("E6:E" & NRows3).Copy Destination:=wshCOMPL.Range("I6:I" & NRows3) Range("F6:F" & NRows3).Delete Range("E6:E" & NRows3).Delete Range("A7", "Q" & NRows3) = v3 Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub2.2KViews0likes9CommentsRe: Speeding Up VBA execution
Hi Hans, Longtime no see. Can I ask you a quick question regarding the fastest possible way to do what the code snippit below does. NRows1 can be very large, even as large or greater than 100000. This rearrangement needs to be done several times at different positions in the overall code of a very large macro. Is there a better and faster way? Thanks. Range("J9:J" & NRows1).Insert Shift:=xlToRight Range("I9:I" & NRows1).Insert Shift:=xlToRight Range("F9:F" & NRows1).Copy Destination:=Range("K9:K" & NRows1) Range("E9:E" & NRows1).Copy Destination:=Range("I9:I" & NRows1) Range("F9:F" & NRows1).Delete Range("E9:E" & NRows1).Delete2.3KViews0likes11CommentsRe: Speeding Up VBA execution
Hi Hans, How do I create a Userform that contains, among other things, a combo box that contains a list of dates with one date to select. The default date that is initially shown must be today's date and the dates that can be selected range from say 14 days before the default of today's date to 14 days ahead of today's date. Of course today's date will change eveyday. It would be preferable not to have this list in any worksheet if possible.3.1KViews0likes15CommentsRe: Speeding Up VBA execution
Thanks Hans, The following section of code searches a sheet containing thousands of entries and places a "del" in the first column of a row satisfying certain conditions, marking that row for ultimately removing that row using say an autofilter deletion. The code is very slow when there are many rows of data. Is there a better more efficient means of implementing this sort of screening for eventual deletion? Thank you. Option Explicit Sub Obsolete() Dim l1Row As Long Dim II As Long Dim JJ As Long Dim SBSO As String SBSO = "SBS Online" l1Row = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row For II = 2 To l1Row For JJ = 3 To l1Row If Range("D" & II) = Range("D" & JJ) And Range("C" & II) = Range("C" & JJ) Then GoTo Label10 GoTo Label100 Label10: If Range("E" & II) <> SBSO And Range("E" & JJ) <> SBSO Then GoTo Label200 If Range("E" & II) = SBSO And Range("F" & II) > Range("F" & JJ) Then Range("A" & II) = "del" If Range("E" & JJ) = SBSO And Range("F" & JJ) > Range("F" & II) Then Range("A" & JJ) = "del" GoTo Label200 Label100: Next JJ Label200: Next II End Sub3.2KViews0likes21CommentsSpeeding Up VBA execution
Hi, The following code searches a large database for cells with a certain fill color and then removes the underlying conditional formatting. The runtime is quite slow. Can this code be made more efficient? Thank you. RGBCode = RGB(192, 0, 0) For Each Cell In Range("D:D") If Cell.Interior.Color <> RGBCode Then GoTo Label10 Cell.Select Selection.FormatConditions.Delete Label10: Next Cell4.7KViews0likes24CommentsSearch & remove fills
How can I search for all cells in say the range A:N for fill colors of RGB(255,255,0) and RGB(255,192,0) and replace them with no fill. The sheet can contain thousands of rows and so a quick way say with autofiltering might be the preferred way? Thank you.732Views0likes1Comment
Recent Blog Articles
No content to show