Progressbar in Excel (VBA)
Maybe I'm trying to do the impossible. Who knows ? I've made a vba uerform to do several things (too long to explain) but what I would like to do is while the progressbar is running to have a small text appear from under the (now) dark blue progress. So it runs from left to right, at a certain point I would like some text to appear letter by letter as the bar gets longer. I tried right clicking on the progress bar and moved it to the back and moved the text to the front. Result is that text stays behing progress bar. Hope this is clear, and hope to see if anyone comes up with a solution. Thanks in advance, Andy
Excel formula to count and sum when value changes?
As a newbie to VB and excel I am having problem with the following, any and all help greatly received: Cell A1 is being populated by a data feed and is frequently changing (every second) in value, both up and down e.g. 10,16,8,12,3 Cell B1 is also being populated by a data feed and is frequently changing (every second) in value both up and down e.g. 8,1,6,12,19 Cell C1 needs to SUM the total amount by which A1 reduces (when it does) and by which B1 increases (when it does) - with the sequences of 10,16,8,12,3 (A1) and 8,1,6,12,19(B1)..... this would be (8+9)+(5+6+7)=35 Cell D1 needs to SUM the total amount by which A1 increases (when it does) and by which B1 decreases (when it does) - with the sequences of 10,16,8,12,3 (A1) and 8,1,6,12,19(B1)..... this would be (6+4)+(7)=17 Hopefully that makes sense! Thank you in advance
Possible Bug: VBA Debugging Breaks After Selecting a Cell with Nested LAMBDA Functions in Excel 365
Hi everyone, I believe I’ve encountered a bug in Excel 365 involving the interaction between LAMBDA functions and the VBA compiler. Issue: When a user selects the formula text of a cell that contains a LAMBDA function calling two or more other LAMBDAs, Excel displays a tooltip saying: “This formula can't be previewed due to custom functions.” Right after this, the VBA compiler stops working properly — step-by-step debugging, breakpoints (Stop, F8, etc.), and other debug features become unresponsive or are ignored entirely. Steps to Reproduce: Create a formula with a nested LAMBDA structure (e.g., one LAMBDA calling others twice). Select the formula text inside the formula bar (or directly in the cell). Notice the warning tooltip from Excel: "This formula can't be previewed due to custom functions." Try debugging any VBA code — you’ll see that debugging no longer works as expected. The only way I’ve found to restore VBA debugging is to close and reopen Excel. Questions: Has anyone else experienced this issue? Is this a known bug, or is there any official workaround? Could this be a conflict between the formula parser and the VBA runtime? Thanks in advance!
Bug Bulletin: Excel Table AutoFormat Defaults Silently Disabled (July 2025 – Current Channel)
Summary: Microsoft’s July 2025 Excel update for Current Channel users silently disabled two critical AutoFormat settings that have been default-on for decades: "Include new rows and columns in table" "Fill formulas in tables to create calculated columns" This change broke table expansion, formula propagation, and VBA logic across thousands of workbooks — with no warning, no changelog entry, and no opt-in control. Impact: Tables no longer auto-expand when typing below or beside them. Formulas in new columns don’t auto-fill, breaking calculated column logic. VBA macros relying on table expansion fail silently. Trainers and analysts face unexpected deliverable errors and loss of student trust. Root Cause: Microsoft introduced Workbook Compatibility Versions and reclassified AutoFormat behaviors as “legacy.” In Version 2 workbooks: AutoFormat defaults are disabled by design. Users must manually re-enable them via: File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type tab Workaround: To restore expected behavior: Go to File > Options > Proofing > AutoCorrect Options Click the AutoFormat As You Type tab Check the following boxes: "Include new rows and columns in table" "Fill formulas in tables to create calculated columns" Click OK Call to Action: This change was made without notice, broke legacy logic, and undermined trust in Excel’s reliability. Microsoft must: Restore these defaults for Version 1 workbooks Surface the change in update notes Provide opt-in control for Version 2 behavior I've spent four days working through this, and Microsoft is aware this is an issue apparently.Significant Testing for a cross tabulated output
https://drive.google.com/file/d/164jBE8C9wNGNnad3gHkaAYhOnYfDd-8n/view Hi all, I have a challenge in performing the significance testing the attached cross-tabulated output for a few questions. I want to test the significance based on Town and Gender variables. Please help
Debug macro for importing and graphing currency prices
Hi All, I am looking to import prices into excel via a userform and then graph the historical prices. I am found some useful code and hints and on the net, however I am still experiencing run errors when run the macro. Any assistance in how to debug the process would be greatly appreciated. kind regards, Callum
Colormatch function in excel (using VBA code)
I am preparing a scoreboard for different projects. 10 KPIs of different weightage to measure each project performance. Each KPI will have either 3 responses (good, bad, ugly). User will update the response as either good or bad or ugly then i will use conditional formatting to color the cell as Green, orange and Red for good, bad and ugly. Once after all the 10 KPIs response received, based on the colormatch function, i need to formulate the scoreboard. For reference, i am giving exactly those 3 colors in some other cells for the formulae reference. But i am facing errors... even though the response of 1 KPI is in green and it is not matching with the green colored cell (reference cell) because, "good" is mentioned in the cell, where as green reference cell is empty. i cannot update these response to reference cells because each kPI have different responses. Can someone suggest me a solution to derive a scoreboard
Password to modify pop-up after VBA timeout
Hi! I have a spreadsheet that uses the Password to modify option from the Save As>Tool>General Options menu to allow the general production folk to access the file in Read-Only and the Engineering team to be able to make edits after entering the password. The file is on a shared network drive and to prevent someone from opening it and leaving it open thus locking everyone else out (multi-shift operations), there is also VBA that will close the file after an hour has passed. This issue I am running into is that the Password to modify pop-up will appear after the workbook has timed out. How do I stop this from happening? Here is my code with some naming changes for anonymity: Module: Dim CloseTime As Date Sub TimeSetting() '60 min from idle start. Change as needed. CloseTime = Now + TimeValue("01:00:00") On Error Resume Next Application.OnTime EarliestTime:=CloseTime, _ Procedure:="CloseDownFile", Schedule:=True End Sub Sub TimeStop() On Error Resume Next Application.OnTime EarliestTime:=CloseTime, _ Procedure:="CloseDownFile", Schedule:=False End Sub Sub CloseDownFile() ' Closes the file WITHOUT saving. Workbooks("WorkbookName.xlsm").Close Savechanges:=False End Sub ThisWorkbook: Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Protect worksheets on close Dim xsheet As Worksheet Dim xpsw As String xpsw = "Password" For Each xsheet In Worksheets xsheet.Protect xpsw Next Worksheets("Database").Visible = False Worksheets("Revisions").Visible = False If ActiveWorkbook.ReadOnly = True Then ' Closes the file WITHOUT asking to save. Workbooks("WorkbookName.xlsm").Close Savechanges:=False Else End If Call TimeStop End Sub Private Sub Workbook_Open() 'This event will execute immediately after the workbook is opened ' Display the UserForm TimeoutWarning TimeoutWarning.Show 'TimeSetting resets the idle timer. See "CloseIdle" module. Call TimeSetting 'Unprotect all sheets if password to modify is entered Dim xsheet As Worksheet Dim xpsw As String xpsw = "Password" If ActiveWorkbook.ReadOnly = False Then Worksheets("Database").Visible = True Worksheets("Revisions").Visible = True For Each xsheet In Worksheets sheet.Unprotect xpsw Next Else End If End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'This event will execute each time the contents of a cell are modified Call TimeStop Call TimeSetting End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 'This event will execute each time the selection changes on a calculation worksheet Call TimeStop Call TimeSetting End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'This event will execute each time the user switches from one worksheet to another Call TimeStop Call TimeSetting End Sub Private Sub Workbook_AfterSave(ByVal Success As Boolean) 'This event will execute immediately after a save command is completed Call TimeStop Call TimeSetting End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 'This event will execute each time a worksheet's data is calculated or recalculated Call TimeStop Call TimeSetting End SubData with Mix of Unique and Duplicate IDs - Create new column that creates Unique IDs for all
I have a data set where I have a mix of unique and duplicate transaction IDs. I am trying to figure out a way to create a MACRO that can complete the following on different data extracts with the same issue Add a column to right of the data I am reviewing called Unique ID Identify the duplicates in the data I am reviewing Pull the duplicate ID into the new column called Unique ID and add -1 to the 1st duplicate, -2 to the 2nd duplicate,.., Note1: The number sequence added to the end of the Unique ID should reset for each duplicate. (i.e., each duplicate should start with -1 rather than the number sequence continuing on. 12345-1, 12345-2, 12345-3. Then next dup should be 12245-1 to sart) Note2: For non duplicate IDs, it can just pull in the ID with -1 or by itself I really appreciate the help here. I'm hoping this doesn't have to be done in manual steps as I know how to do that but hoping I can automate this or at least as much of as it as possible Note: My company doesn't have the Microsoft Scripts functionality incase there's an option similar to this in there. ZJ
