Category Archives: Code Snippets

Here you will find various snippets and code examples that I have used to make some thing in my life a little easier to deal with.

VBA EXCEL: Duplicate Comments to Matching Cells of Another Spreadsheet in the Same Workbook

I have an Excel workbook with 13 protected sheets, January through December plus a Summary that needed the same cell comments for each month.

There were 2 options…

  • Type comments into each sheet manually; then update 11 sheets (Feb-Dec) by hand whenever comments need changing 😬
  • Add VBA code to the workbook that automatically duplicates cell comments from January (unprotected) to February through December (protected) when those sheets are activated 😎
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim xSheetProtected As Boolean
    
    'Can add Case conditions for varying sheet passwords
    Select Case Sh.Index
        Case 1
            'Unprotected
            xPassword = ""
        Case Else
            xPassword = "Pa$$w0rd"
    End Select
    
    If Sh.ProtectContents Then
        If xPassword = "" Then
            MsgBox "Missing password for protected sheet!", vbExclamation
            Exit Sub
        Else
            xSheetProtected = True
            On Error GoTo Invalid_Sheet_Password
            Sh.unprotect Password:=xPassword
        End If
    End If
    
    'Copy January header cell comments to February-December
    If Sh.Index > 1 And Sh.Index < Application.Sheets.Count Then
        'Columns containing comments (eg: row 3)
        For Each xCell In Worksheets(1).Range("3:3")
            If xCell.Text <> "" Then
                Range(xCell.Address).ClearComments
                If Not xCell.Comment Is Nothing Then
                    Range(xCell.Address).AddComment xCell.Comment.Text
                End If
            Else
                'Stop on empty cell
                GoTo Workbook_SheetActivate_End
            End If
        Next
    End If

Workbook_SheetActivate_End:
    If xSheetProtected = True Then
        Sh.Protect Password:=xPassword
    End If
    Exit Sub

Invalid_Sheet_Password:
    MsgBox "Password provided for """ & Sh.Name & """ sheet does not match!", vbExclamation, "Invalid Password"
    Exit Sub
    
End Sub

If you got this far, I assume you can follow VBA code. That said, the code should be self-explanatory with my code comments are naming conventions 😉

Add Pattern Support for HTML5 Input Type Number with jQuery

Ran into an issue earlier today that I could not figure out and ended up asking my second question on StackOverflow in seven years.

I was trying to use <input type="number" pattern="[0-9]{8}"> to enforce 8-digit numbers while allowing leading zeros. This was being done on an optional field (no required attribute) and I could not understand why it was bypassing my pattern on the populated field (eg: value “1234” was accepted, pattern ignored).

Peter B. over at SO (kudos) pointed me to the MDN reference that provided the reason… number input does not support pattern attribute

By design! While their reasoning makes sense from a general perspective, the min/max attribute workaround does not help where leading zeros are acceptable (eg: min="00000001"is the same as min="1" for obvious reasons). Unfortunately I need all 8-digits for my purposes. I also tried using minlength and maxlength attributes as a hail-mary to no avail.

Here is a jQuery shim I wrote for my main script file thereby adding pattern support the <input type="number"> Continue reading Add Pattern Support for HTML5 Input Type Number with jQuery

How to Continue Using Buttons with “data-loading-text” in Bootstrap 4 with jQuery

While converting a Bootstrap 3 template design for use with Bootstrap 4, I noticed they removed stateful buttons (eg: “loading” and “reset” button states) from BS4! Granted it was mentioned in BS3, but I never noticed the deprecated warning 🙁

The deprecated code for showing a button’s loading state was $('#selector').button('loading') and to return the button to its original state you would use  $('#selector').button('reset') . Continue reading How to Continue Using Buttons with “data-loading-text” in Bootstrap 4 with jQuery

Add Mouse Wheel Support to Bootstrap Carousel with jQuery

I was adding mouse wheel events to a ul element with the CSS rule overflow:hidden to emulate mouse wheel scrolling when no scrollbar was present.

The links in the unordered list also trigger a Bootstrap 4.1 Modal that contains a Bootstrap Carousel. So I decided to add the same functionality to my modal carousel gallery. Continue reading Add Mouse Wheel Support to Bootstrap Carousel with jQuery

MYSQL: Get Flag Emoji Unicode from ISO 3166-1 Alpha-2 Country Codes

Today I had to figure out the most efficient way to generate Emoji from ISO 3166-1 Alpha-2 country codes such as US, GB, etc. I opted to go for a for 100% SQL solution.

The most efficient way to convert each ASCII letter to their Unicode Regional Indicator Symbol Letter that I determined is provided below (simplified)… Continue reading MYSQL: Get Flag Emoji Unicode from ISO 3166-1 Alpha-2 Country Codes

Create a Windows Batch (.bat) File to Test Your Internet Connectivity

I have been having issues with my ISP‘s WiFi router, so I decided to create a batch file I could run as-needed to check internet connectivity.

@echo off
cls
set "router=192.168.0.1"
set /p "router=Enter router IP address or press [ENTER] for default [%router%]: "
@ping %router% -n 1 | FIND "Reply"
@ping google.com -n 1 | FIND "Reply"
@ping bing.com -n 1 | FIND "Reply"
echo Press any key to exit...
pause >nul

Save that as `ping.bat` and put in somewhere convenient. Then… Continue reading Create a Windows Batch (.bat) File to Test Your Internet Connectivity

SEO URLs for OpenCart Using a “web.config” File on Windows IIS Server

I always suggest Linux hosting for any OpenCart installations. The main OC developers either don’t know much about Windows IIS or neglect to include the necessary SEO URL configuration file for it to work.

This omission results in “not found” 404 (dead link) errors when you visit a page with an SEO URL assigned after enabling SEO URLs in your OpenCart store settings.
Continue reading SEO URLs for OpenCart Using a “web.config” File on Windows IIS Server

PHP Function to Sum or Concatenate Associative Array Values

Here is a quick function I created to merge multiple arrays and sum or concatenate the values thereof. If the values are numeric they will be added together (sum). Otherwise the values will be appended to each other (concatenation).

function arrayMergedSumConcat() {
	$out = array();

	$num_args = func_num_args();
	
	$arg_list = func_get_args();
	
	for ($i = 0; $i < $num_args; $i++) { $in = $arg_list[$i]; foreach ($in as $key => $value) {
			if (isset($out[$key])) {
				if (is_numeric($in[$key]) && is_numeric($out[$key])) {
					$out[$key] += $in[$key];
				} else {
					$out[$key] .= $in[$key];
				}
			} else {
				$out[$key] = $in[$key];
			}
		}
	}
	
	return $out;
}

Stop Annoying favicon.ico 404 errors

If you’re like me, you don’t like cluttering your main folder with unnecessary files. This is why I hate that some browsers are still looking for ‘favicon.ico’ in the main folder even when you indicate in <head> that’s not where the agent should be looking…

Here is a way to eliminate those PITA 404’s from your error logs… Continue reading Stop Annoying favicon.ico 404 errors