User Profile
NikolinoDE
Platinum Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Re: Best practice using same OneDrive Personal Vault on 2 PCs
PIN-only unlock on multiple PCs is not supported for OneDrive Personal Vault. You must authenticate the first time on every new PC After that, PIN/Windows Hello works normally No way to bypass the initial Authenticator approval No setting exists to make every PC unlock using only a PIN I hope that helped you a little.56Views0likes0CommentsRe: Context menu immediately closing after opening
This behaviour (context menu instantly closing, focus snapping back to the workbook, VBA window refusing to stay focused, etc.) is almost always caused by one of three things: a broken or invisible UserForm that is being kept modeless a stuck event handler (even if macros are disabled now) a corrupted workbook window state Check if a hidden/active UserForm is blocking input Check for a stuck event handler Check for window corruption Open the workbook in Excel Safe Mode Check for an endless BeforeRightClick event If none of the above fixes it Does it happen with only one workbook? Does it happen even after you copy all sheets to a new workbook? Does it occur if you upload the file here? Your description matches either a hidden modeless UserForm or Application.Interactive = False. Hope this helps you.6Views0likes0CommentsRe: Filter cells in stead of full rows by color
Excel cannot hide individual cells or “collapse” them so that only colored cells remain visible while others in the same column disappear. Filtering always operates at the row level, never the cell level. But there are workarounds that let you display only the cells of a specific color per column. A - Workaround Use a helper column to mark colored cells This works if you need a reliable, filterable method. 1. Add a helper column next to each data column Example: For column C, add column H labelled “C-red?”. 2. Use a formula + VBA function to detect color Excel formulas cannot detect cell color directly, but a tiny VBA function can: Step A — Insert this small VBA function Press ALT+F11 Insert → Module Paste: Function CellColorIndex(rng As Range) As Long CellColorIndex = rng.Interior.ColorIndex End Function Close the editor. Step B — In your helper column enter: =CellColorIndex(C2) Copy down. Step C — Filter the helper column by the color index of red You will now see only the cells in that column that have the matching fill color. B - Workaround This lets you jump through only the colored cells, but not hide the others. Steps: Press Ctrl + F Click Options Click the Format… button → Choose Choose Format From Cell Click a red cell Click Find All You’ll get a list of only the red cells. Clicking any result will select that cell. Rows are not hidden — this is navigation only, not filtering. Hope this helps you.1View0likes1CommentRe: Can't get OneDrive with all my data on new computer
You don’t need to “transfer” OneDrive from your old laptop at all. Your files already live in the cloud — you just need to sign in correctly on the new computer. If OneDrive isn’t syncing on the new laptop, it’s almost always one of a few simple issues. Make sure you're signed into the SAME Microsoft account This is the #1 issue. On your new laptop: Click Start → Settings → Accounts → Email & Accounts Look for the Microsoft account that appears on the old laptop (typically ends in @outlook.com, hotmail.com, or your personal email) If it’s not there → click Add account and sign in with the correct one Then: Click the cloud icon (OneDrive) on the taskbar If it asks to sign in → use the same Microsoft account as the old laptop If you sign in with a different Microsoft account by accident, it creates a new, empty OneDrive. Check if OneDrive is installed New Windows machines usually have it, but if not: Download: OneDriveSetup.exe from Microsoft’s site Then run it and sign in. Let OneDrive recreate the folder automatically When you sign in, OneDrive will ask where you want to store your OneDrive folder. Just click Next and accept the default folder. Do NOT copy your old OneDrive folder manually — this causes sync problems. Once logged in, your files should start downloading automatically. These instructions apply to the current OneDrive sync client on Windows 10 and Windows 11 — the unified Microsoft OneDrive (Standalone Sync Client) that Microsoft has shipped since ~2017. Hope this helps you5Views0likes0CommentsRe: Cleaning up data with Macros
Below is a simple, reliable way to create a macro-powered button that clears rows older than 3 months in Excel. Sub ClearOldEntries() Dim ws As Worksheet Dim lastRow As Long Dim checkDate As Date Dim i As Long ' Change to your sheet name if needed Set ws = ThisWorkbook.Sheets("Sheet1") ' Date threshold: 3 months before today checkDate = DateAdd("m", -3, Date) ' Find last used row lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Loop from bottom to top to avoid skipping rows For i = lastRow To 2 Step -1 ' assumes row 1 is headers If IsDate(ws.Cells(i, "A").Value) Then If ws.Cells(i, "A").Value < checkDate Then ws.Rows(i).ClearContents ' clear contents only ' or use: ws.Rows(i).Delete — if you want to delete the entire row End If End If Next i End Sub10Views0likes0CommentsRe: Move files from Account A to Account B 165 GB data
Share → “Add to my OneDrive” → Copy In Account A: Share a top‑folder (containing your 165 GB) with Account B (give edit permissions). In Account B: Log into OneDrive web, go to Shared → find the folder from Account A → choose “Add to my OneDrive” (so it appears under Account B’s OneDrive). Then from the B side: Copy the folder (within B’s OneDrive) into the correct location (so it becomes files owned by B). Once confirmed, you can delete the folder/contents from A, freeing up space. Pros: Minimises upload from your local machine (because much of the transfer happens server‑side). Uses existing OneDrive sharing and copy features. Cons: With many files / large files this can still be slow or unreliable (users report issues). Some metadata (like exact “modified by”) may not preserve perfectly. You’ll want to check thoroughly that all 200k files and folder structure copied correctly before deleting. Use a third‑party cloud‑to‑cloud migration tool How: Use a tool such as MultCloud or CBackup or similar, which connect both OneDrive accounts and move files server‑to‑server. Example: CBackup supports “Sync Public Cloud to Public Cloud” for OneDrive → OneDrive. cbackup.com Example: MultCloud supports “Cloud Transfer” between two OneDrive accounts. app.multcloud.com Pros: Minimal local bandwidth usage (the data flows between cloud accounts). Often more controllable (can schedule, monitor transfers, resume/catch errors). You keep your machine free during the bulk transfer. Cons: Some tools may cost money (or have free‐tier limitations). You must trust a third‑party with access to both accounts (check their security/privacy). Still may run into API limits or throttling by OneDrive if many files/large files are moved. Check whether they preserve all metadata or just the files. Choose first (Share → Add to My OneDrive → Copy) because it uses mostly cloud transfer and avoids your upload bottleneck. Before you delete anything from Account A: Verify the file count, folder structure, and total GB size in Account B equals what was in A. Spot‑check large video files to ensure they open OK. If you encounter reliability issues (lots of errors, incomplete copy, very slow): switch to Method 2 — pick a reputable cloud‑migration service. Once confirmed solidly, delete from Account A to free up space. Important things to watch Ensure Account B has sufficient free space (≥ 165 GB) before starting. Large files (e.g., ISOs/videos) can be tricky in one go. Consider doing them in chunks (e.g., move videos folder first, then photos). Metadata: If you care about “Modified date” or version history, check the tool/method preserves them — many consumer migrations lose version history. Deletion: After transfer, you may need to empty the Recycle Bin in Account A to truly free up space. Time: Even cloud‑to‑cloud may take multiple hours, maybe a day or two depending on number of files and API throughput. Backup: Until you’re sure everything is transferred, keep the original safely in Account A (don’t delete prematurely). My answers are voluntary and without guarantee! Hope this will help you. Was the answer useful? Mark as best response and like it! This will help all forum participants.0Views0likes0CommentsRe: Le séparateur de milliers ne fonctionne pas dans Excel sur Mac
Ah, je vois exactement le problème. Sur Mac, Excel gère différemment certains formats XML strict par rapport aux formats classiques, et ça affecte notamment le séparateur de milliers. Le problème est un bug/limitation connue de Excel pour Mac avec le format XML strict. Les solutions pratiques sont : Convertir en .xlsx classique (meilleur si possible). Contourner avec un format personnalisé. Passer par Windows ou Libre Office pour réenregistrer le fichier si le Mac ne permet pas le changement de format. Mes réponses sont données à titre indicatif et sans garantie ! J'espère que cela vous aidera. Cette réponse vous a-t-elle été utile ? N'hésitez pas à la marquer comme meilleure réponse et à cliquer sur « J'aime » ! Cela sera utile à tous les participants du forum.32Views0likes1CommentRe: Opening workbooks
Using VBA to Open a Specific Sheet Open your workbook. Press Alt + F11 to open the VBA editor. In the Project Explorer (usually on the left), find your workbook, expand it, and double-click ThisWorkbook. In the code window that appears, paste this code: Private Sub Workbook_Open() Sheets("Instructions").Activate End Sub Replace "Instructions" with the exact name of your tab. Sheet names are case-insensitive but must match exactly. Save your workbook as a macro-enabled workbook (.xlsm). Close and reopen the workbook. It should now always open on the Instructions sheet. Without VBA (Manual Navigation) Excel itself doesn’t have a built-in “open to this sheet” setting without VBA. But you can: Make the Instructions tab the first tab in your workbook (drag it to the far left). Save the workbook while the Instructions tab is active. This often works if users aren’t reopening via a shortcut that restores the last sheet, but it’s not guaranteed. Or… You can hide all other sheets and in the Instruction sheet you can make links to the other sheets. Tip: If you navigate to another sheet via a link, there should also be a link in that sheet to return to the previous sheet. My answers are voluntary and without guarantee! Hope this will help you. Was the answer useful? Mark as best response and like it! This will help all forum participants.2Views0likes0CommentsRe: Drop-down list with data validation rule
You can do this using Data Validation with a custom formula in Excel. Here’s a step-by-step guide: Select B5 (the cell where the amount is entered). Go to the menu: Data → Data Validation → Data Validation… In the Settings tab: Allow: Custom Formula: enter a formula that checks both the drop-down selection and the amount. Enter the formula Assuming your offer types are in K5, the formula is: =IF(K5="offer1", AND(B5>=200, B5<=1000), TRUE) Test it: Choose "offer1" in K5 and try entering 2000 in B5 → You should see your alert. Choose a different offer type → Any amount is allowed. My answers are voluntary and without guarantee! Hope this will help you.16Views0likes0CommentsRe: Excel formula to take time off from a time range
Understand the logic You have time ranges and corresponding break deductions: Hours Worked (C2) Break Deduction 4:00 – 5:59 15 minutes 6:00 – 7:59 30 minutes 8:00 – 8:59 60 minutes 9:00 – 11:59 90 minutes You want D2 = C2 - break. Correct Excel Formula: =IF(AND(C2>=TIME(4,0,0),C2<=TIME(5,59,0)), C2-TIME(0,15,0), IF(AND(C2>=TIME(6,0,0),C2<=TIME(7,59,0)), C2-TIME(0,30,0), IF(AND(C2>=TIME(8,0,0),C2<=TIME(8,59,0)), C2-TIME(1,0,0), IF(AND(C2>=TIME(9,0,0),C2<=TIME(11,59,0)), C2-TIME(1,30,0), C2)))) Make sure C2 is in time format (e.g., [h]:mm if it can exceed 24 hours). TIME(hours, minutes, seconds) works the same on Mac. My answers are voluntary and without guarantee! Hope this will help you.17Views0likes0CommentsRe: Excel + IRM: Allow VBA to edit protected cells / insert rows without granting "Full Control"
You want a fully stand-alone Excel workbook, protected via IRM/MIP, where VBA can modify protected cells or insert rows, without granting users Full Control (so far i understand). Excel + IRM / MIP Limitations There is no native Excel/MIP setting that allows macros to bypass protected cells under IRM without Full Control. Any attempt to do Sheet.Unprotect or InsertRows via VBA will fail unless the user has Full Control permissions. Why UserInterfaceOnly = True fails UserInterfaceOnly allows macros to edit protected sheets without unprotecting them, but this does not override IRM restrictions. IRM protection is applied at a level below the Excel object model — macros cannot bypass it unless the file is opened by a user with Full Control. There is no Excel-native way to allow macros to bypass IRM/MIP cell protections without Full Control. IRM is designed intentionally to enforce governance, including programmatic access restrictions. UserInterfaceOnly = True only works with normal Excel protection, not IRM-protected workbooks. For fully stand-alone usage, you either: Avoid IRM for the editable areas and rely on sheet protection + passwords, or Use a split model where the IRM master file is read-only, and macros write to a separate unprotected workbook. If your goal is stand-alone VBA automation with cell editing under IRM, Microsoft’s current model does not support it without granting Full Control. Any workaround requires either: Relaxing IRM to allow normal sheet protection, or External service identity (Power Automate, service account) to handle edits under IRM. Excel-native alternatives (stand-alone) Given that you cannot bypass IRM programmatically without Full Control, the closest Excel-native options are: Approach Pros Cons Use normal sheet protection (password) instead of IRM for cells/macros UserInterfaceOnly=True works; macros can insert rows/update formulas Does not prevent copying file outside the domain Split workbook into master (IRM) + editable local sheets Sensitive data remains IRM-protected; users edit local sheet macros Adds complexity, requires sync logic VBA temporarily unprotect with a password Stand-alone macros work on protected cells Password must be embedded (security risk) I apologize that I cannot offer a reasonable solution, but the available options do not allow it. My answers are voluntary and without guarantee! Hope this will help you.8Views1like0CommentsRe: Excel VBA Updates not functioning as intended
Thanks for that thoughtful follow-up — really appreciate the careful read. You’re absolutely right: most of the edits were cleanup (naming consistency, structure, better error handling, and avoiding duplicated Exit Sub branches). But yes, the two points you noted — using Application.InputBox with Type:=1 and separating vDate as an actual Date variable — are what primarily affect the matching logic. The root of the original issue was indeed a type mismatch during the .Find operation. The original code was searching for a text string like "10/01/2025", while the worksheet cells contained true date serials formatted as m/d/yyyy. Since .Find with LookIn:=xlValues compares underlying values, not visible formatting, the string "10/01/2025" didn’t match the numeric date value. By explicitly converting both sides to a true date (vDate = CDate(NM)) and searching for that date value, Excel’s internal comparison works correctly regardless of how the cell is formatted (whether 1/4/2025 or 01/04/2025). The other side benefit of the rewrite is that it ensures formulas pasted into the “Slide Prep” sheet are treated as real formulas — not text — by resetting number formats before assigning them. I’ll definitely share if anything else interesting turns up, but yes, it seems to come down to how .Find interprets date strings versus serials depending on locale and cell formatting. Thanks again for the thoughtful comment — this kind of cross-check is exactly why the community is great. … I rewrite it in a slightly more bulletproof version with error handling and dynamic column handling for Slide Prep. That would make it future-proof….i hope 🙂i dont test it. Option Explicit Sub CMLUpdateV4() Dim wb As Workbook Dim wsData As Worksheet, wsSlide As Worksheet Dim rngDateRow As Range, rngFound As Range Dim dtInput As Variant, NM As String, vDate As Date Dim blnValidDate As Boolean Dim col As Long, lastCol As Long ``` On Error GoTo Cleanup '--- Optimize performance Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual '--- Close other workbooks For Each wb In Application.Workbooks If Not (wb Is ThisWorkbook) Then wb.Close SaveChanges:=False Next wb '--- Ask user for a valid date blnValidDate = False Do dtInput = Application.InputBox("Please Enter File Submission Month as MM/DD/YYYY:", _ "Date Entry", , , , , , 1) If dtInput = False Then MsgBox "Update cancelled by user.", vbInformation GoTo Cleanup End If If IsDate(dtInput) Then NM = Format(CDate(dtInput), "MM/DD/YYYY") vDate = CDate(NM) blnValidDate = True Else MsgBox "Invalid Date Format. Please re-enter in MM/DD/YYYY format.", vbExclamation End If Loop Until blnValidDate '--- Reference worksheets Set wsData = ThisWorkbook.Worksheets("Data") Set wsSlide = ThisWorkbook.Worksheets("Slide Prep") '--- Check if date already exists in Data row 2 Set rngDateRow = wsData.Rows(2) Set rngFound = rngDateRow.Find(What:=vDate, LookIn:=xlValues, LookAt:=xlWhole) If Not rngFound Is Nothing Then MsgBox "Date " & NM & " already exists on the Data sheet. Please review.", vbInformation GoTo Cleanup End If '======================= ' Update Data tab '======================= With wsData .Columns("C:C").Insert .Columns("D:D").Copy .Columns("C:C").PasteSpecial xlPasteAll .Range("C2").Value = vDate .Range("C2").NumberFormat = "m/d/yyyy" ' Clear old content blocks .Range("C3:C6,C16:C19,C29:C32,C42:C45,C55:C58,C68:C71").ClearContents ' Ensure column O is static values .Columns("O:O").Copy .Columns("O:O").PasteSpecial xlPasteValues End With '======================= ' Update Slide Prep tab '======================= With wsSlide .Rows("2:2").Insert .Range("A2").Value = vDate .Range("A2").NumberFormat = "m/d/yyyy" ' Copy formulas from previous row (B3:H3) .Range("B3:H3").Copy .Range("B2:H2").PasteSpecial xlPasteAll ' Reset number format for new formula columns lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column .Range(.Cells(2, 9), .Cells(2, lastCol)).NumberFormat = "General" ' Insert dynamic XLOOKUP formulas B2 to last column For col = 2 To lastCol .Cells(2, col).Formula = "=XLOOKUP(" & .Cells(1, col).Address(False, False) & _ ",Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" Next col ' Convert row 14 to values (per original code) .Rows("14:14").Copy .Rows("14:14").PasteSpecial xlPasteValues End With '--- Success message MsgBox "New File Date " & NM & " added and sheets updated." & vbCrLf & _ "Proceed to Data tab to record Enrollment Line Counts.", vbInformation ``` Cleanup: '--- Restore defaults Application.CutCopyMode = False Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic ``` If Err.Number <> 0 Then MsgBox "An error occurred: " & Err.Description, vbCritical End If ``` End Sub2Views1like1CommentRe: Formula broke, need commas back. (complicated)
If your formula currently uses ; like: =SUM(A1;B1;C1) Replace it with commas: =SUM(A1,B1,C1) You can do a quick Find/Replace (Ctrl+H) in the formula bar for ; → ,. Without the formula and the digital environment (operating system, Country, storage medium, etc.), unfortunately, we can only guess, and I'm not good at guessing. My answers are voluntary and without guarantee! Hope this will help you.16Views0likes0CommentsRe: Custom Date format Excel 365 ddd/mm/yy
Unfortunately, Excel for Web currently does not support creating or editing custom number/date formats (as far as I know). You can only select from the pre-defined list of formats under “Number Format → Date.” This is a known limitation — even though the web app preserves existing custom formats created in the desktop app. So if you try to type ddd - dd mmm - yy (or your desired Mon - 01 Jan - 25) directly in the web UI, it won’t accept it. Workarounds: Use a helper column with a TEXT formula If you want to stay fully within Excel for Web, use a formula instead: =TEXT(A2, "ddd - dd mmm - yy") Then your output will look exactly the same, e.g.: Mon - 01 Jan - 25 You can even hide the raw date column and just display this formatted version in your to-do list. This is dynamic — if you change the date in A2, the formatted result updates automatically. Use a helper column with a TEXT formula If you want to stay fully within Excel for Web, use a formula instead: =TEXT(A2, "ddd - dd mmm - yy") Then your output will look exactly the same, e.g.: Mon - 01 Jan - 25 You can even hide the raw date column and just display this formatted version in your to-do list. This is dynamic — if you change the date in A2, the formatted result updates automatically. This is a suggestion. My answers are voluntary and without guarantee! Hope this will help you.8Views0likes0CommentsRe: VBA trouble with Export as Fixed Format
Classic Excel-on-Mac VBA snag — especially with ExportAsFixedFormat and SharePoint/OneDrive paths. Sub PrintAllVariablePayoutPDFs_MacSafe() Dim DropDown As Range Dim IndivName As Range Dim NamesList As Range Dim PDFName As String Dim PDFPath As String Dim FullPath As String ' Disable screen updates for speed Application.ScreenUpdating = False ' Dropdown location Set DropDown = Range("G2") ' List of all names (from validation) Set NamesList = Evaluate(DropDown.Validation.Formula1) ' Use a valid local path - ensure it exists first PDFPath = "/Users/dominiccronshaw/Library/CloudStorage/OneDrive-COMPANY/GTM Operations/Commissions/2025 Q3/Payout PDFs/" If Right(PDFPath, 1) <> "/" Then PDFPath = PDFPath & "/" If Dir(PDFPath, vbDirectory) = "" Then MsgBox "PDF output folder not found: " & PDFPath, vbCritical Exit Sub End If ' Loop through all names in list For Each IndivName In NamesList DropDown.Value = IndivName.Value DoEvents ' let Excel update dependent cells ' Refresh PDF name each time (in case K4 changes) PDFName = Range("K4").Value FullPath = PDFPath & PDFName & ".pdf" ' Optional debug check Debug.Print "Saving PDF to: " & FullPath ' Export as PDF (Mac-safe) On Error Resume Next ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ FileName:=FullPath, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False If Err.Number <> 0 Then MsgBox "Error saving PDF for " & IndivName.Value & vbCrLf & _ "Path: " & FullPath & vbCrLf & _ "Error: " & Err.Description, vbCritical Err.Clear End If On Error GoTo 0 Next IndivName Application.ScreenUpdating = True MsgBox "All PDFs exported successfully!", vbInformation End Sub Make sure the output folder exists. Excel won’t create directories. Avoid spaces or punctuation in folder names if possible. The local OneDrive path on Mac must be mounted under /Users/.../Library/CloudStorage/OneDrive-YourOrgName/. If the script appears to “do nothing”, open the VBA Immediate Window (Cmd+Option+I) to check the Debug.Print output — it will show exactly where it’s trying to save. This is a suggestion. My answers are voluntary and without guarantee! Hope this will help you.0Views0likes0CommentsRe: Excel + IRM: Allow VBA to edit protected cells / insert rows without granting "Full Control"
Goal Supported by IRM? Prevent external sharing Fully supported via IRM/MIP Lock structure but allow VBA edits Not possible without Full Control Allow VBA edits under service context Possible with automation identity Sheet protection with macro access Use Excel protection, not IRM Recommended Enterprise Model Keep IRM label on master workbook (read-only for users). Build a macro-enabled “editor” workbook with internal security (password-protected sheets). Use the macro to write updates through: SharePoint REST API, or Excel Online Connector (Power Automate) using a service account with Full Control. That preserves your data sovereignty and corporate controls without granting users Full Control rights. Simple VBA that POSTs to Power Automate HTTP endpoint This is a lightweight client example. It does not impersonate elevated rights — it just sends data. Sub SendToPA() Dim http As Object Dim url As String Dim sJSON As String Dim dateVal As String url = "https://prod-XX.westus.logic.azure...." ' Power Automate HTTP trigger URL (use managed auth in prod) dateVal = Format(Range("A2").Value, "MM/DD/YYYY") sJSON = "{" & Chr(34) & "user" & Chr(34) & ":" & Chr(34) & Environ("USERNAME") & Chr(34) & "," & _ Chr(34) & "date" & Chr(34) & ":" & Chr(34) & dateVal & Chr(34) & "}" Set http = CreateObject("MSXML2.XMLHTTP") http.Open "POST", url, False http.setRequestHeader "Content-Type", "application/json" ' If using a static shared key (not recommended), include header ' http.setRequestHeader "x-api-key", "<secret-from-keyvault>" http.send sJSON If http.Status = 200 Then MsgBox "Update submitted successfully." Else MsgBox "Error: " & http.Status & " - " & http.responseText End If End Sub Do NOT embed long-lived secrets in client-side VBA. Use short lived tokens or require the VBA call to include user identity so Power Automate can validate and prompt for approval. This is a suggestion. My answers are voluntary and without guarantee! Hope this will help you.3Views1like2CommentsRe: Excel VBA Updates not functioning as intended
Option Explicit Sub CMLUpdateV3() Dim wb As Workbook Dim wsData As Worksheet, wsSlide As Worksheet Dim rngDateRow As Range, rngFound As Range Dim dtInput As Variant, NM As String, vDate As Date Dim blnValidDate As Boolean '--- Optimize performance Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual '--- Close all other workbooks to avoid confusion For Each wb In Application.Workbooks If Not (wb Is ThisWorkbook) Then wb.Close SaveChanges:=False Next wb '--- Ask user for a valid date (InputBox Type:=1 ensures date entry) blnValidDate = False Do dtInput = Application.InputBox("Please Enter File Submission Month as MM/DD/YYYY:", _ "Date Entry", , , , , , 1) If dtInput = False Then MsgBox "Update cancelled by user.", vbInformation GoTo Cleanup End If If IsDate(dtInput) Then NM = Format(CDate(dtInput), "MM/DD/YYYY") vDate = CDate(NM) blnValidDate = True Else MsgBox "Invalid Date Format. Please Re-Enter in MM/DD/YYYY format.", vbExclamation End If Loop Until blnValidDate '--- Reference worksheets Set wsData = ThisWorkbook.Worksheets("Data") Set wsSlide = ThisWorkbook.Worksheets("Slide Prep") '--- Check if date already exists in Data row 2 Set rngDateRow = wsData.Rows(2) Set rngFound = rngDateRow.Find(What:=vDate, LookIn:=xlValues, LookAt:=xlWhole) If Not rngFound Is Nothing Then MsgBox "Date " & NM & " already exists on the Data sheet. Please review.", vbInformation GoTo Cleanup End If '======================= ' Update Data tab '======================= With wsData .Columns("C:C").Insert .Columns("D:D").Copy .Columns("C:C").PasteSpecial xlPasteAll .Range("C2").Value = vDate .Range("C2").NumberFormat = "m/d/yyyy" 'Clear old content blocks .Range("C3:C6,C16:C19,C29:C32,C42:C45,C55:C58,C68:C71").ClearContents 'Ensure column O is static values .Columns("O:O").Copy .Columns("O:O").PasteSpecial xlPasteValues End With '======================= ' Update Slide Prep tab '======================= With wsSlide .Rows("2:2").Insert .Range("A2").Value = vDate .Range("A2").NumberFormat = "m/d/yyyy" 'Copy formulas from previous row (B3:H3) .Range("B3:H3").Copy .Range("B2:H2").PasteSpecial xlPasteAll 'Reset cell format to General to ensure formulas calculate .Range("I2:M2").NumberFormat = "General" 'Insert XLOOKUP formulas Dim col As Long For col = 2 To 13 'B to M .Cells(2, col).Formula = "=XLOOKUP(" & .Cells(1, col).Address(False, False) & _ ",Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" Next col 'Convert row 14 to values (per original code) .Rows("14:14").Copy .Rows("14:14").PasteSpecial xlPasteValues End With '--- Confirm success MsgBox "New File Date " & NM & " added and sheets updated." & vbCrLf & _ "Proceed to Data tab to record Enrollment Line Counts.", vbInformation Cleanup: '--- Restore defaults Application.CutCopyMode = False Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub This is a suggestion🙂. Hope it helps.2Views0likes3CommentsRe: Accidentally deleted ALL of my files
Check your Recycle Bin Even though you said the deletion bypassed it, it’s still worth verifying: Right-click Recycle Bin → Open. If it’s empty, try the OneDrive online Recycle Bin too: Go to onedrive.live.com Click Recycle Bin on the left. Check both primary and second-stage recycle bins (scroll to bottom → “Second-stage Recycle Bin”). Use File Recovery Tools If the files were hard-deleted, you can often still recover them with data recovery software, provided they haven’t been overwritten. Microsoft’s own tool: Windows File Recovery (free from Microsoft Store) Command example (replace drive letter accordingly): winfr C: D: /regular /n *.* Or use reputable third-party tools. Check Backups and Cloud Versions If you’ve ever used: Windows File History (Control Panel → File History) OneDrive Personal Vault / Backup Time Machine / External backup drive Check there — they might contain snapshots of your user folders. Windows 10/11 often “moves” Desktop, Documents, Pictures, etc. into OneDrive automatically when you set up cloud backup. Even though they looked local, they were being synced to (and stored in) your OneDrive directory. Deleting OneDrive removed the entire structure. When you reinstalled or reactivated OneDrive, it showed only files that were already synced to the cloud — not the ones that never finished syncing or were deleted locally before upload.11Views0likes0CommentsRe: Excel/VBA Worksheet_Change function running old version
What you’re experiencing is almost always not Excel showing a ghost old version, but rather some subtle issue with how the Worksheet_Change event is being triggered or where the code actually resides. You can add a simple Debug.Print to confirm exactly which code is running: Private Sub Worksheet_Change(ByVal Target As Range) Debug.Print "Worksheet_Change fired" MsgBox "Error B" End Sub If you see "Worksheet_Change fired", the code is being executed. If you still see “Error: A”, then some other code or add-in is interfering. Otherwise it would be helpful if the VBA code was included.27Views0likes0CommentsRe: Data Import issue???
Here’s a drop-in replacement formula that will work no matter if the imported data is text or numbers, and will always return a valid drift direction (0–360°). =MOD(VALUE(TRIM(B28))+180,360) My answers are voluntary and without guarantee! Hope this will help you.54Views0likes0Comments
Recent Blog Articles
No content to show