Checkboxes: currently unreconcilable differences between Excel Online and Excel 2024
I started working on a database that I thought would be simple. I start with a product list. Each inventory item in that list needs a quality control check from each of 6 departments. In each row, I wanted to set a color range from red to green on the background depending on how far along the check process is coming: for example, the row is red when 0 checks have been completed, at any 3 out of 6 checks complete it's at yellow, and if all 6 checks are fulfilled/complete, the item background changes color to green. So when I visually scroll the list, with my dyslexia I can more easily see at what phase the progress is at. Those are the basics behind why I decided on checkmarks. I started with Excel 2021 and had some limitations regarding checkboxes. I had to study how to switch to developers mode to access checkboxes in the first place, checkbox placement is less than user friendly, and 1600 rows and 6 columns of checkboxes lagged my PC so badly I had to wait about a minute after scrolling the document before the screen caught up. I began to suspect I was missing some new functions with my older Excel, so then decided to try Excel Online mostly for functionality testing. Excel Online worked flawlessly from the get-go. First off, I could select the whole column and select checkbox on the whole column with one click, which was super slick. I got my 6x conditional formatting setup almost on the first attempt super easy. Then, I went to set a floating watch window so could monitor real-time values of which departments were getting more checks completed. Bummer, Excel Online doesn't support floating watch window. Since I also really wanted to finish work on this file locally offline, I decided to buy the newest version of Office 2024 thinking I was missing some functions with an older version. After installing Office 2024, I downloaded the excel file from Excel Online to my PC, and opened it with my shiny new Excel, and the first thing it did was change all my checkboxes to boolean true or false. After much online searching, it also appears the most efficient way to add checkboxes to 1600 rows in 6 columns in Excel '24 is to learn how to use VBA macros to automate the process. I WAS able get my floating watch window up and running with my autosum values from each column, so there's that... I am a pitiful beginner to Excel. Having said that, I keep reviewing, looking for something I'm missing, but a reasonable time-efficient solution is just out of my reach. Why would Excel '24 not let me work on my database locally the same way I was working with it in Excel Online? I would hardly believe the dev team overlooked this. Or has my solution been misdirected from the start? I'll appreciate any input and thanks for reading my novel. BR, X
New formulas like FILTER and UNIQUE not appearing on macOS
Hello, I have MacBook pro with macOS Monterey 12.6.6 and I am running Excel 365 16.82. I know there is a more recent version of macOS and Excel which requires 13.0 or newer, but I don't think my version is that old that it doesn't have UNIQUE and FILTER? When I type =FILTER I get the error #NAME? and it is not suggesting any functions with similar names. Why is this the case?
Excel VBA Object Variable or With Block Variable Not Set Error
Hello! I'm trying to figure out where I'm going wrong with my code. Basically I want my code to decide what protections to initiate and how to proceed based on the data in column C on the 'LOA' tab. My table starts at A8 (headers) and is dynamic. My code should be allowing user entry from C9 to last row and only allowing users to edit columns A-B & H-O if there is data selected in Column C for that row. I had to adjust some of the ranges and that's where I started receiving the error. My original code allowed users to edit the headers because there was data in C8 so I tried to update the code to prevent that. My current code is below: Private Sub Workbook_Open() Dim ws As Worksheet Dim wsRef As Worksheet Dim lastRow As Long Dim lastRowRef As Long ' Set worksheet references Set ws = ThisWorkbook.Sheets("LOA") Set wsRef = ThisWorkbook.Sheets("References") ' Unprotect workbook and sheets ThisWorkbook.Unprotect "123456" ws.Unprotect "123456" wsRef.Unprotect "123456" ' Sort data on LOA sheet lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ws.Sort.SortFields.Clear ws.Sort.SortFields.Add Key:=ws.Range("C8:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ws.Sort .SetRange ws.Range("A8:O" & lastRow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Set up data validation for Employee Name lastRowRef = wsRef.Cells(wsRef.Rows.Count, "A").End(xlUp).Row With ws.Range("C9:C" & lastRow).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=References!$A$2:$A$" & lastRowRef End With ' Set formulas for columns D-G ws.Range("D9:D" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",2,FALSE)" ws.Range("E9:E" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",3,FALSE)" ws.Range("F9:F" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",4,FALSE)" ws.Range("G9:G" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",5,FALSE)" ' Find the last row with data in column C lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' Set the range for column C Set dataRange = ws.Range("C9:C" & lastRow) ' Loop through each cell in column C For Each cell In dataRange If Not IsEmpty(cell) Then ' Unlock corresponding cells in A, B, and H-O ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = False ws.Range("H" & cell.Row & ":O" & cell.Row).Locked = False Else ' Lock corresponding cells in A, B, and H-O ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = True ws.Range("H" & cell.Row & ":O" & cell.Row).Locked = True End If Next cell ws.Range("C9:C" & lastRow).Locked = False ' Protect LOA sheet ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True ' Protect References sheet wsRef.Protect "123456" ' Protect workbook ThisWorkbook.Protect "123456" End Sub Option Explicit Private Const WARNING_MESSAGE As String = "Warning: You are attempting to {0} data." & vbNewLine & _ "That is not allowed in this spreadsheet. Please press 'ESC' to return to your work." Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim lastRow As Long On Error GoTo ErrorHandler lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row If Not Intersect(Target, Me.Range("C9:C" & lastRow)) Is Nothing Then Application.EnableEvents = False Me.Unprotect "123456" UpdateCellLockStatus Target Me.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True End If ExitSub: Application.EnableEvents = True Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description, vbCritical, "Error" Resume ExitSub End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.CutCopyMode = False Then Select Case Application.CutCopyMode Case xlCut ShowWarningMessage "cut" Case xlCopy ShowWarningMessage "copy" End Select End If End Sub Private Sub UpdateCellLockStatus(ByVal Target As Range) Dim cell As Range Dim affectedRange As Range For Each cell In Intersect(Target, Me.Range("C9:C" & lastRow)) Set affectedRange = Union(Me.Range("A" & cell.Row & ":B" & cell.Row), _ Me.Range("H" & cell.Row & ":O" & cell.Row)) affectedRange.Locked = (cell.Value = "") Next cell End Sub Private Sub ShowWarningMessage(ByVal action As String) MsgBox Replace(WARNING_MESSAGE, "{0}", action), vbExclamation + vbOKOnly, "Data Modification Warning" End Sub Private Sub Worksheet_Change_Paste(ByVal Target As Range) If Not Application.CutCopyMode = False Then ShowWarningMessage "paste" End If End Sub Option Explicit Sub AddNewRowToTable() Dim ws As Worksheet Dim tbl As ListObject Dim table_object_row As ListRow ' Set references to the worksheet and table Set ws = ThisWorkbook.Worksheets("LOA") ' Replace "Sheet1" with your sheet name Set tbl = ws.ListObjects(1) ' Replace "Table1" with your table name ' Unprotect workbook and sheets ws.Unprotect "123456" Set table_object_row = tbl.ListRows.Add table_object_row.Range(1, 1).Value = "" ' Protect LOA sheet ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True End Sub My old code that worked is below: Private Sub Workbook_Open() Dim ws As Worksheet Dim wsRef As Worksheet Dim lastRow As Long Dim lastRowRef As Long ' Set worksheet references Set ws = ThisWorkbook.Sheets("LOA") Set wsRef = ThisWorkbook.Sheets("References") ' Unprotect workbook and sheets ThisWorkbook.Unprotect "123456" ws.Unprotect "123456" wsRef.Unprotect "123456" ' Sort data on LOA sheet lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ws.Sort.SortFields.Clear ws.Sort.SortFields.Add Key:=ws.Range("C9:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ws.Sort .SetRange ws.Range("A9:AL" & lastRow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Set up data validation for Employee Name lastRowRef = wsRef.Cells(wsRef.Rows.Count, "A").End(xlUp).Row With ws.Range("C9:C" & lastRow).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=References!$A$2:$A$" & lastRowRef End With ' Set formulas for columns D-G ws.Range("D9:D" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",2,FALSE)" ws.Range("E9:E" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",3,FALSE)" ws.Range("F9:F" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",4,FALSE)" ws.Range("G9:G" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",5,FALSE)" ' Find the last row with data in column C lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' Set the range for column C Set dataRange = ws.Range("C1:C" & lastRow) ' Loop through each cell in column C For Each cell In dataRange If Not IsEmpty(cell) Then ' Unlock corresponding cells in A, B, and H-AL ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = False ws.Range("H" & cell.Row & ":AL" & cell.Row).Locked = False Else ' Lock corresponding cells in A, B, and H-AL ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = True ws.Range("H" & cell.Row & ":AL" & cell.Row).Locked = True End If Next cell ws.Range("C9:C" & lastRow).Locked = False ' Protect LOA sheet ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True ' Protect References sheet wsRef.Protect "123456" ' Protect workbook ThisWorkbook.Protect "123456" End Sub Option Explicit Private Const WARNING_MESSAGE As String = "Warning: You are attempting to {0} data." & vbNewLine & _ "That is not allowed in this spreadsheet. Please press 'ESC' to return to your work." Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler If Not Intersect(Target, Me.Range("C:C")) Is Nothing Then Application.EnableEvents = False Me.Unprotect "123456" UpdateCellLockStatus Target Me.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True End If ExitSub: Application.EnableEvents = True Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description, vbCritical, "Error" Resume ExitSub End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.CutCopyMode = False Then Select Case Application.CutCopyMode Case xlCut ShowWarningMessage "cut" Case xlCopy ShowWarningMessage "copy" End Select End If End Sub Private Sub UpdateCellLockStatus(ByVal Target As Range) Dim cell As Range Dim affectedRange As Range For Each cell In Intersect(Target, Me.Range("C:C")) Set affectedRange = Union(Me.Range("A" & cell.Row & ":B" & cell.Row), _ Me.Range("H" & cell.Row & ":AL" & cell.Row)) affectedRange.Locked = (cell.Value = "") Next cell End Sub Private Sub ShowWarningMessage(ByVal action As String) MsgBox Replace(WARNING_MESSAGE, "{0}", action), vbExclamation + vbOKOnly, "Data Modification Warning" End Sub Private Sub Worksheet_Change_Paste(ByVal Target As Range) If Not Application.CutCopyMode = False Then ShowWarningMessage "paste" End If End Sub Option Explicit Sub AddNewRowToTable() Dim ws As Worksheet Dim tbl As ListObject Dim table_object_row As ListRow ' Set references to the worksheet and table Set ws = ThisWorkbook.Worksheets("LOA") ' Replace "Sheet1" with your sheet name Set tbl = ws.ListObjects(1) ' Replace "Table1" with your table name ' Unprotect workbook and sheets ws.Unprotect "123456" Set table_object_row = tbl.ListRows.Add table_object_row.Range(1, 1).Value = "" ' Protect LOA sheet ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True End Sub I'm also trying to add code that will delete all of the data in a row (without removing data validations or formatting) when the data in Column C is deleted. I tried the following code, but I wasn't sure how to implement it. Option Explicit Private Const WARNING_MESSAGE_DEL As String = "Warning: You are attempting to delete data in Column C." & vbNewLine & _ "This action will clear the entire row. Do you want to proceed?" Private Sub Worksheet_BeforeDelete(ByVal Target As Range, Cancel As Boolean) Dim intersectRange As Range ' Check if the deletion involves Column C Set intersectRange = Intersect(Target, Me.Columns("C")) If Not intersectRange Is Nothing Then If MsgBox(WARNING_MESSAGE_DEL, vbExclamation + vbYesNo, "Deletion Warning") = vbNo Then Cancel = True End If End If End Sub Private Sub ClearRowExceptColumnC(ByVal rowNumber As Long) ' Clear cells to the left of Column C Me.Range("A" & rowNumber & ":B" & rowNumber).ClearContents ' Clear cells to the right of Column C Me.Range("D" & rowNumber & ":" & Me.Cells(rowNumber, Columns.Count).Address).ClearContents End Sub I'm very much a novice when it comes to VBA, but I love the customizability and am trying to learn more. If you have any other advice it would be very much appreciated!
Check symbol in drop down list
I want to have the check and cross symbol appear in the drop down list. I have created two cells, one with the check symbol (Wingdings font) and cross symbol (Wingdings font). When are create the drop down list the symbols appear as "ü" and "û". is there a way to have the list show the symbols?#NAME? error continues to appear
I am constantly getting #NAME? error on files that appear to have correct formulas. This generally happens if I work on a file in the browser and then open it in excel. Example below shows an xlookup formula error. The formula is referencing a tab in the same workbook. This also happens on other formulas. Ive never seen this happen before until recently. Is there something I can do to prevent this from happening?
How to return counts of text cells across multiple worksheets in same workbook?
I'm working in Excel 365. Windows environment. Working in the Excel app but will be posting to a shared drive for multi-user data entry. 38 worksheets, one for each office. What I need is to be able to return the total # of records in the data set that are marked as 'Complete', 'Pending' and 'Urgent' (from a drop-down is one cell of each record), by office and the Rep's name that made the entry (also from drop-down). I would also like to be able to return the total entries across all worksheets by Rep Name, so I can see which offices/Reps are making the most/least entries. I'm not very savvy with using Power Query. I've tried to do it, but I think it requires that there are no blank cells in the records. It keeps making tables in the query that don't actually exist. Tried it using 6 worksheets, just to test it, and the appended data set contains way more tables that I added. I'm not bad with pivot tables. If anyone has an idea of how I could accomplish this, I sure would appreciate it! Thanks in advance for any assistance. :)
Counting unique values with multiple criteria
I'm a pretty basic Excel user, and trying to gather some data about our application's clients. Suppose I have this: MONTH IRRELEVANT1 CLIENT IRREL2 SITE STATUS JAN X 1 Y 101 ACTIVE JAN X 1 Y 102 CLOSED JAN X 2 Y 201 ACTIVE JAN X 3 Y 301 CLOSED JAN X 3 Y 401 ACTIVE JAN X 4 Y 402 CLOSED JAN X 4 Y 403 ACTIVE JAN X 4 Y 405 CLOSED JAN X 4 Y 406 ACTIVE JAN X 5 Y 501 ACTIVE FEB X 1 Y 103 CLOSED FEB X 2 Y 201 ACTIVE FEB X 3 Y 301 CLOSED FEB X 2 Y 202 ACTIVE FEB X 1 Y 102 CLOSED FEB X 2 Y 203 ACTIVE MAR X 3 Y 302 ACTIVE MAR X 4 Y 402 CLOSED MAR X 5 Y 502 ACTIVE MAR X 6 Y 601 CLOSED MAR X 4 Y 403 ACTIVE MAR X 3 Y 305 CLOSED MAR X 2 Y 205 ACTIVE MAR X 1 Y 102 ACTIVE MAR X 2 Y 201 CLOSED MAR X 3 Y 302 ACTIVE (Note the "irrelevant" columns interspersed) Questions I want to answer: By month, what are the number of clients that have at least 1 active site By month, what are the number of active sites (across all clients) Every Monday, I'm going to be adding in about 10,000 rows. I had something working for both of these that was using a FILTER with multiple criteria (matching on MONTH and matching on STATUS=ACTIVE) but after about 3 months, when I load in new data, Excel runs out of resources with those filters. So that's not gonna work. I got kind of close with this: =COUNTIFS(C2:C27, UNIQUE(C2:C27), A2:A27, "JAN", F2:F27, "ACTIVE") That generates a spill range: 1 1 1 2 1 0 I don't actually understand how the first 2 parameters work, TBH. Output seems to be saying "client 1 occurs 1 time, 2 occurs 1 time, 3 occurs 1 time, 4 occurs 2 times, 5 occurs 1 time, 6 occurs 0 times). So it's close...Note that I cannot do this: =COUNT(COUNTIFS(C2:C27, UNIQUE(C2:C27), A2:A27, "JAN", F2:F27, "ACTIVE")). I have to generate the results into a table, and then count that. Not ideal. Any Excel wizards out there able to point me in the right direction?"Sorry , something went wrong"
Can anyone help me with this error? In one of the Excel online workbook, which has a lot of rows and links. since around 2 weeks ago, everytime anyone double clicks any cells with hyperlink, it will show this error message afterwards and if you press OK the workbook will reload. Thank you.
