Automatically highlight selected Excel row

Here’s a simple script that makes it easier to visually scan lengthy Excel spreadsheets. If you spend a lot of time in Excel spreadsheets, you’ll find this useful.

What you’ll need

  • .XLSM Excel workbook
  • Macros enabled
Excel spreadsheet with color highlighting
Excel VBA script that automatically highlights current selected row
Excel VBA script that automatically highlights current selected row

Although I like this above script, you do have to hard code a few values. Here’s another Exel video that is more straight forward. This method also highlights the columns. This video, assumes you already know how to enable the developer tab and create formulas however.

'Visual Basic for applications code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With ThisWorkbook.Names("CurrentRow")
    .Name = "CurrentRow"
    .RefersToR1C1 = "=" & ActiveCell.Row
'above "&" should be the ampersand symbol. 
    End With

End Sub


How to check a website for chain redirects/circular redirects

I recently migrated one of my sites to HTTPS. Although this seems like a simple change, the site went down because circular redirects were accidentally created.

screenshot of page not working due to circular redirect
Screenshot of site down due to too many redirects.

There are a couple of tools you can use to identify chain/circular redirects. Not only will misbehaved redirects bring down your site, they can slow it down, and also adversely impact your search engine rankings. So, here are a couple of ways to identify these types of redirects.

  1. Using a command prompt. (this is more slick approach that will earn you some street cred with your IT folks) Type. “curl -L -i https://www.angelskills.com/wp”. Make sure you replace my blog with your own website:). If your redirects were setup correctly, you should only get HTML source code of the page.
Screenshot of Windows Command prompt showing the Curl command and 50 redirects
Screenshot of Windows Command prompt showing the Curl command and 50 redirects

 Now, if it’s not setup correctly, you will see a long list of redirects(reference screenshot above). In my case there were 50. This was not good. For a summarized review of this result remove the “-i” so this  “curl -L  https://www.angelskills.com/wp”. Now we have since resolved my circular redirect issue so I recreated an example for you to try out with curl. Use this link. “http://www.angelskills.com/test/a.html “. Most browsers are smart enough to know that this will cause an infinite loop but you can still see the magic by using curl or the Chrome extension below.

Screenshot of Windows Command prompt showing the Curl command. This was when I had a circular redirect in place.
Screenshot of Windows Command prompt showing the Curl command. This was when I had a circular redirect in place.

The other approach which isn’t as grandiose is to use a boring Chrome extension. “Redirect Path” works nicely.  

Screenshot of Redirect Path Chrome Extension tool
Screenshot of Redirect Path Chrome Extension tool

How to mute your computer microphone with a single keyboard key

With everyone working remote these days, it is nice to follow phone/video conference etiquette and mute your microphone if you are not the speaker. I was surprised to learn that there isn’t a dedicated mute key on most keyboards to mute your microphone. The mute button on my laptop and keyboard only mute the speakers. So here’s a script I put together that mutes your microphone. It relies on registry values so it is more dependable than hacking your system by emulating send keys values. This works best with keyboards or mice with customization capabilities. I’m using a Logitech keyboard. Alternatively, you can also use keyboard shortcuts.

What you’ll need before starting.
* Administrative or elevated privileges to your computer
* A little determination

Step 1: Identify your microphone registry values. Yes, this isn’t for the faint of heart.
Step 2: Modify Powershell script below. Replace “Placeholder” with your register value. Save file as “mute.ps1”.
Step 3. Create a simple .bat script that calls the PowerShell script with the proper execution permissions. (note, you may also need to run this code in an administrative PowerShell window. “set-executionpolicy unrestricted” if the code is still not executing. Call the file “mute.bat”.
Step 4: Update Folder ownership within Regedit for the folder containing the “microphone array” item. Change it from “System” to all users and applications.
Step 5: Customize keyboard key to reference this “mute.bat” script when the button is pressed.

Note, I implemented this on Windows 10 Pro. Version 1903. The script does modify a single Registry value so you may want get buy-in from your IT department prior to incorporating this.

Code for step 2: Here’s the Powershell code. Note, you will only have to change the “PLACEHOLDER” value.

$micStatus="Enabled"
$Key = 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\MMDevices\Audio\Capture\{PLACEHOLDER}'

if(Test-Path $key){
	$item = Get-ItemProperty -path $Key -Name DeviceState
	$item.DeviceState
}
else
{
	Write-Host 'path not found'
}
if( $item.DeviceState -Match "0")
        {
 
        Write-Host 'Mic Enabled'
                Set-ItemProperty -Path $Key -Name "DeviceState" -Value '1' -Force | Out-Null
				$micStatus="Enabled"
        }
else
 
        {
        Write-Host 'Mic Disabled'
 
                Set-ItemProperty -Path $Key -Name "DeviceState" -Value '10000001' -Force | Out-Null
				$micStatus="Disabled"
 
        }
		
Add-Type -AssemblyName System.Windows.Forms
$global:balmsg = New-Object System.Windows.Forms.NotifyIcon
$path = (Get-Process -id $pid).Path
$balmsg.Icon = [System.Drawing.Icon]::ExtractAssociatedIcon($path)
$balmsg.BalloonTipIcon = [System.Windows.Forms.ToolTipIcon]::Warning
$balmsg.BalloonTipText = 'Mic status of apps not updated'
$balmsg.BalloonTipTitle = "Microphone $micStatus"
$balmsg.Visible = $true
$balmsg.ShowBalloonTip(50000)

“mute.bat” code for Step 3

powershell.exe -executionpolicy bypass -file "C:\Users\C\Documents\work\mute.ps1"

Step: 4 – Error message you will see if you don’t update permissions.

You may likely get the above error message when you attempt to run the PowerShell script directly. In this case, you will need to update the Owner within the registry. After doing this your script should work properly.

Before mapping the mute.bat script to the keyboard, attempt running the script by double clicking the .bat file. After the script executes, you should see a notification message at the bottom right of Windows. You can also see the notification if you expand the notifications Window. Screenshot below. Navigate to sound settings to ensure the microphone was indeed disabled.

screenshot of microphone disabled notification
Screenshot of microphone disabled notification
screenshot of Windows 10 sound menu
Navigate to Sound setting by right clicking on the speaker icon in your toolbar then selecting “Open Sound settings”
Screenshot of Microphone when it still enabled.
Windows 10 Screenshot of Microphone disabled
Screenshot of Microphone when it is disabled. Notice, the input device is missing.

This script can also be retro fitted to disable web cams with a single keyboard press. You’ll just need to identify the registry values for the webcam. Dr Scripto also has an alternative method for disabling the webcam.

How to determine if a webpage is a Single Page app

I’ve been working increasingly more with Single page app webpages. Single page apps or SPAs look and behave a lot like traditional webpages. For example, the URL can change between page/view transitions, and additional network requests can be made as a user interacts with content. Because of this, it is difficult to tell whether a webpage was built as a SPA.

Unfortunately, there isn’t a single way to know whether a page is a SPA because it comes down to how the page was built. Here are some of the signs to look for.

  1. Framework – check if the page uses one of these popular frameworks. VueJS, React, Angular and others. Just because a webpage uses one of these frameworks doesn’t mean the page was built as a SPA however. Wappalayzer is useful browser extension that helps identify many useful things about a webpage such as JavaScript frameworks, CMS, CDN and many more useful information about the tech stack. Give the extension a try.
  2. SPAs are usually used on pages with dynamic content. For example, a product listing page that lists out products from a database, checkout process, lead forms, etc.
  3. URL change – Although it’s a best practice to update the URL if substantial sections of the webpage are updated, this can be overlooked. Some SPAs will have the same URL even though the contents of the page changes.
  4. Page doesn’t reload(no new pageload). Only certain sections of the page change when a user performs an action. For example, you can use your browser’s inspector to change a small section of the page such as the navigation and after making this change you can navigate around different pages of the site and you’ll notice that your navigation change persist through the different pages. This wouldn’t happen with traditional webpage. On traditional webpages, inspector changes revert after a user navigates to a new page. Try it out! This is a good test that I use to help identify SPA pages. Although this works for this website, there is no guarantee that other SPAs won’t repaint the navigation so it’s good to experiment with your own website by changing different elements using your browser’s inspector. Reference screenshots below.
Screenshot of SPA website before I deleted a few navigation elements using the browser's inspector.
Screenshot of SPA website before I deleted a few navigation elements using the browser’s inspector.
Screenshot showing Chrome Debugger - removing UNAV element
Screenshot showing Chrome Debugger – removing UNAV element
Screenshot showing Chrome debugger - Showing persistent navigation change in Single Page App
Screenshot showing Chrome debugger – Showing persistent navigation change in Single Page App