User Profile
Jan_Wijninckx
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: How to remove the default folders from the left pane of File Explorer in Windows11
Hi Shahab, I am at my wits end, nothing works to get rid of these Views like Videos in the navigation pane of explorer. I have tried: ; Videos - this sort of thing used to work in Windows 10 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\FolderDescriptions\{35286a68-3c57-41a1-bbb1-0eae73d76c95}\PropertyBag] "ThisPCPolicy"="Hide" [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Explorer\FolderDescriptions\{35286a68-3c57-41a1-bbb1-0eae73d76c95}\PropertyBag] "ThisPCPolicy"="Hide" Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{A0953C92-50DC-43bf-BE83-3742FED03C9C} Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{A0953C92-50DC-43bf-BE83-3742FED03C9C} (Default) REG_SZ CLSID_ThisPCMyVideosRegFolder HiddenByDefault REG_DWORD 1 HideIfEnabled REG_DWORD 1 some say 22ab9b9 - no diff System.IsPinnedToNameSpaceTree REG_DWORD 0 HKEY_CURRENT_USER\Software\Classes\CLSID\{A0953C92-50DC-43bf-BE83-3742FED03C9C} System.IsPinnedToNameSpaceTree REG_DWORD 0 I have tried what you wrote and deleted them I looked in group policy but nothing there. Any ideas? Looking forward to your thoughts. AI is out of its depth I need real intelligence :) (added) I have now also set Computer\HKEY_CLASSES_ROOT\CLSID\{A0953C92-50DC-43bf-BE83-3742FED03C9C} Computer\HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{A0953C92-50DC-43bf-BE83-3742FED03C9C} System.IsPinnedToNameSpaceTree REG_DWORD 0 And no change from that either (had to change the permissions for this)9Views0likes0CommentsExcel 365: dd-mmm-yyyy now gives Sept instead of Sep
Excel 365: dd-mmm-yyyy now gives Sept instead of Sep — that breaks the documented format standard. Who thought this was a good idea? They linked it to the Unicode locale standard. But it violates the formatting of "mmm". Work arounds for USA locale format as [$-409]dd-mmm-yyyy → 05-Sep-2025 UK locale [$-809]dd-mmm-yyyy → 05-Sep-2025 AU [$-C09]dd-mmm-yyyy → 05-Sep-2025 NZ [$-1409]dd-mmm-yyyy → 05-Sep-2025 Problem is this is not portable. PLease can someone reach out to the devs and get this reverted? Formulas trying to pick out Sep & year from the string now will break.1KViews0likes6CommentsRe: Formating a part of the cell text in result of a function?
Don't think it be done. Formulas can't be formatted other than adding new-lines. You can try setting a custom format, but the minute your $A$1 cell value changes the format needs to be changed too. You can do it programmatically using VBA to collate the text, then write that to the cell and by code set the format. Alternatively you have 3 cells for the result, where $A$1 is in the middle cell and that cell is formatted as bold/93Views0likes0CommentsRe: Formula Excel Help
Since you have it so neatly organized this is the perfect use case for a pivot table! Pivot out the count or the sum I would also use the pivot table as your data will inevitably lead to someone forgetting to enter one of the rows, so you'll have two entries for one and perhaps four for another. The pivot table would immediately show that.182Views0likes0CommentsRe: MS Ribbon - how to set its visible property to true/false in office programmes?
dodger1 I had exactly the same issue. ChatGPT couldn't solve it either, but gave me plenty of pointers. So that this doesn't go to waste, here a 3 VBA routines that do what the buttons do: Sub RibbonShowFull() If Application.CommandBars("Ribbon").Visible Then Debug.Print "Ribbon is visible, check by how much" With Application.CommandBars("Ribbon").Controls(1) If .Height > 40 And .Height < 155 Then Debug.Print "Shows tabs functions only, toggle" CommandBars.ExecuteMso "MinimizeRibbon" End If End With Else Debug.Print "Is not showing, switch Ribbon on" Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", True)" With Application.CommandBars("Ribbon").Controls(1) If .Height < 155 Then Debug.Print "Shows tabs functions only, toggle" CommandBars.ExecuteMso "MinimizeRibbon" End If End With End If End Sub Sub RibbonShowTabsOnly() If Application.CommandBars("Ribbon").Visible Then Debug.Print "Ribbon is in view, check by how much" With Application.CommandBars("Ribbon").Controls(1) If .Height > 155 Then Debug.Print "Shows full tabs, toggle" CommandBars.ExecuteMso "MinimizeRibbon" End If End With Else Debug.Print "Ribbon is hidden, switch on" Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", True)" With Application.CommandBars("Ribbon").Controls(1) If .Height > 155 Then Debug.Print "Shows full tabs, toggle" CommandBars.ExecuteMso "MinimizeRibbon" End If End With End If End Sub Sub RibbonHideFromView() If Application.CommandBars.GetVisibleMso("TabHome") Then Debug.Print "Ribbon is visible, switch off" Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", False)" End If End Sub Hope that helps!994Views0likes1CommentRe: workbook and sheet name via formula
The best solution which has been listed high above y someone else remains: =SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D1), "!"), "'", "" ), "=", "") With D1 containing e.g.: ='3 Sheet 3'!D2 This works for sheet names like "3 Sheet" or "Sheet 3" or "Sheet3" And resolves the single quotes or not single quotes. Very simple, very elegant6.8KViews1like0CommentsRe: workbook and sheet name via formula
Hi MikeFromCanada 🙂 (I like the name). If you want backward compatibility avoid Lambda expressions! Lambda is a new feature of the past 12 months or so. If you have someone with Excel 2019 or 2016 standalone version, Lambda does not exist. Excel 365 - do you mean the online version? For the online version / SharePoint you need a very different function, as CELL() does not work in those ! For the desktop Office 365 version, I have tried the: =MID(CELL("filename",D1),FIND("]",CELL("filename",D1))+1,32) on a sheet I named "3 Sheet", and that works. This formula will also work if you have 2016 or 2019 or 2022 stand-alone versions. If you want to be versatile across Web / SharePoint and Desktop 365 or fixed versions then you need to use this: =SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D1), "!"), "'", "" ), "=", "") where the cell content of D1 ='3 Sheet'!D2 , and the format of D1 is set to 0;0; That solution is universal. It is a bit messy as it uses 3 cells. You can use the Formulas> Evaluate Formula function to check how it works. This solution was posted somewhere higher up in this thread too. Happy days to you from New Zealand 🙂3.2KViews0likes2CommentsRe: workbook and sheet name via formula
Dear MikeFromCanada , you didn't address this to me so I'll happily butt out; still, why are you trying to make it so complicated? Perhaps you wish to use a Lambda function to only have one parameter? This is the shortest function that just works: =MID(CELL("filename",D1),FIND("]",CELL("filename",D1))+1,32) where d1 contains a reference like =Sheet1!D2, with formatting set to 0;0; The easiest to create that reference is to type (in D1) = , select another sheet, then select Sheet1 again and click on D2. The trick to avoid issues and improve maintainability is to keep it simple?! Before you create the Lamba, put your formula in a cell, then use the Formulas > Evaluate formula function to step through what is going wrong. Once debugged, turn it into a Lambda5.7KViews0likes4CommentsRe: workbook and sheet name via formula
rcstha - you just need to put the formula on each sheet. Just select which environment your workbook has to work in: Desktop or SharePoint/Web. Desktop: =MID(CELL("filename",'Sheet1'!A2),FIND("]",CELL("filename",'Sheet1!A2))+1,32) (thanks NuggetSA ) SharePoint/Web: =SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D2), "!"), "'", "" ), "=", "") with D2 containing: ='Sheet1'!D1 formatted as 0;0; The latter is easy to create: place your cursor on D2 type = switch to another sheet, then switch back to Sheet1, and click on D1. Done.5.4KViews0likes2CommentsRe: workbook and sheet name via formula
I know it is called 365, but there are differences for the Web and SharePoint; if you just look at https://learn.microsoft.com/en-us/sharepoint/dev/general-development/supported-and-unsupported-features You'll see there is a difference between web-services and SharePoint It doesn't stop at functions, VBA is severely impaired in SharePoint, and yes the web of course I just wish Microsoft would stop their folly on the JavaScript for Office Web as non of that works on the desktop, and vv. Here are some o the function that don't work in SharePoint (and maybe Web) https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List and sadly I have lost my list of VBA deficiencies when using SharePoint. Let's not argue about product naming, lets focus on what it does on the web, SharePoint and OS/filesystem5.4KViews0likes1CommentRe: workbook and sheet name via formula
Bloggs20: Brilliant solution, thank you so much. Replace the ; with a , and it works I modified it to this to the name without the quotes: B1='Sheet 1'!A1 format as 0;0; C1=SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(B1), "!"), "'", "" ), "=", "") And boom, works11KViews1like0Comments
Recent Blog Articles
No content to show