Last active
February 4, 2026 23:52
-
-
Save tomfleet/2cd3217bee1c28bc9eaf1e60e4b7a905 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Public Sub InsertRangeDropdown() ' bind me to a macro key or something | |
| Dim rangeList As String | |
| ' Adding visual separators | |
| rangeList = "---CAPS---,F,mF,uF,nF,pF,---RES---,Ohm,KiloOhm,MegaOhm,GigaOhm" | |
| With Selection.Validation | |
| .Delete | |
| .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=rangeList | |
| .IgnoreBlank = True | |
| .InCellDropdown = True | |
| .ShowError = False ' Set to False so headers don't trigger "Invalid Entry" | |
| End With | |
| End Sub | |
| Private Sub CalculateComponentValue(ByRef unitCell As Range) | |
| Dim valLeft As Variant | |
| Dim factor As Double, baseUnit As String | |
| Dim result As Double | |
| If unitCell.Value = "" Then Exit Sub | |
| valLeft = unitCell.Offset(0, -1).Value | |
| If Not (IsNumeric(valLeft) And valLeft <> "") Then Exit Sub | |
| Select Case unitCell.Value | |
| ' --- Capacitance --- | |
| Case "F": factor = 0: baseUnit = "Farad" | |
| Case "mF": factor = -3: baseUnit = "Farad" | |
| Case "uF": factor = -6: baseUnit = "Farad" | |
| Case "nF": factor = -9: baseUnit = "Farad" | |
| Case "pF": factor = -12: baseUnit = "Farad" | |
| ' --- Resistance --- | |
| Case "Ohm": factor = 0: baseUnit = "Ohm" | |
| Case "KiloOhm": factor = 3: baseUnit = "Ohm" | |
| Case "MegaOhm": factor = 6: baseUnit = "Ohm" | |
| Case "GigaOhm": factor = 9: baseUnit = "Ohm" | |
| ' --- Headers / Non-Selectable (Abuse case) --- | |
| Case "---CAPS---", "---RES---": Exit Sub | |
| Case Else: Exit Sub | |
| End Select | |
| result = valLeft * (10 ^ factor) | |
| Application.EnableEvents = False | |
| unitCell.Offset(1, -1).Value = result | |
| unitCell.Offset(1, 0).Value = baseUnit | |
| unitCell.Offset(1, -1).NumberFormat = "0.00E+00" | |
| Application.EnableEvents = True | |
| End Sub | |
| Private Sub Worksheet_Change(ByVal Target As Range) | |
| If Target.Cells.Count > 1 Then Exit Sub | |
| Dim dropdownCell As Range | |
| ' Check if current cell is the dropdown | |
| If HasUnitValidation(Target) Then | |
| Set dropdownCell = Target | |
| ' Check if cell to the right is the dropdown | |
| ElseIf HasUnitValidation(Target.Offset(0, 1)) Then | |
| Set dropdownCell = Target.Offset(0, 1) | |
| Else | |
| Exit Sub | |
| End If | |
| ' Run calculation logic using the identified dropdownCell | |
| CalculateComponentValue dropdownCell | |
| End Sub | |
| ' Helper function to check for the condit | |
| Private Function HasUnitValidation(ByRef rng As Range) As Boolean | |
| On Error Resume Next | |
| ' Check if it's a list; you could also check for specific content in Formula1 | |
| HasUnitValidation = (rng.Validation.Type = xlValidateList) | |
| On Error GoTo 0 | |
| End Function | |
| Public Sub ClearRangeDropDown() | |
| ' Ensure we are dealing with a range selection | |
| If TypeName(Selection) <> "Range" Then Exit Sub | |
| ' .Delete on a Range object clears validation for all cells in that range. | |
| Selection.Validation.Delete | |
| End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment