Create the Sidebar menu in the excel file with the help of Claude AI

You can find the Prompt file from the below link.

Below is a detailed prompt

# Step-by-Step Prompt to Recreate SidebarNavigation.bas

> **Goal:** Reproduce the exact VBA code in `SidebarNavigation.bas` word-for-word (logic, variable names, values, structure). Comments may differ in wording but must convey the same meaning.

—

## MASTER PROMPT
*(paste this entire block to an AI or use it as your specification)*

—

Write a complete Excel VBA standard module named **`SidebarNavigation`**. The module attribute at the very top must be:

“`
Attribute VB_Name = “SidebarNavigation”
“`

Start with `Option Explicit`. The module must contain **no public variables** — only module-level private constants, followed by procedures and functions in the exact order listed below.

—

## STEP 1 — Module-Level Private Constants

Declare all constants at the top of the module using `Private Const`. Group them exactly as follows:

### 1b. Colour placeholder constant (type `Long`)
| Constant Name | Value |
|—|—|
| `CLR_BG_DARK` | `1741096` |

> This constant is declared for documentation only. All actual colours in the code use the `RGB()` function directly.

> These prefixes are used both to name shapes when creating them and to identify sidebar shapes for deletion. Any shape whose name starts with `”SB_”` is considered a sidebar shape.

—

## STEP 2 — Public Sub `CreateSidebar()`

This is the **only public entry point**. Declare it as `Public Sub CreateSidebar()`.

**Variables to declare:**
“`vb
Dim wb As Workbook
Dim ws As Worksheet
Dim sidebarWPt As Double
Dim sidebarHPt As Double
Dim colAWidthCh As Double
“`

**Logic in exact order:**

1. `Set wb = ActiveWorkbook`
2. Guard: `If wb Is Nothing Then` → show a `MsgBox` with text `”No active workbook found. Please open a workbook first.”` using `vbExclamation`, then `Exit Sub`.
3. Convert sidebar dimensions from inches to points (multiply each by `72`):
– `sidebarWPt = SIDEBAR_WIDTH_INCHES * 72`
– `sidebarHPt = SIDEBAR_HEIGHT_INCHES * 72`
4. Suppress screen flicker:
– `Application.ScreenUpdating = False`
– `Application.EnableEvents = False`
5. `On Error GoTo Cleanup`
6. Call `PrepareColumnA wb, sidebarWPt`
7. Loop: `For Each ws In wb.Worksheets` → call `BuildSidebarOnSheet ws, wb, sidebarWPt, sidebarHPt` → `Next ws`
8. **Label:** `Cleanup:`
9. Restore application state:
– `Application.ScreenUpdating = True`
– `Application.EnableEvents = True`
10. Error check: `If Err.Number <> 0 Then` → show `MsgBox` with `Err.Description` and `vbCritical`. `Else` → show success `MsgBox` showing `wb.Worksheets.Count` and text `”sheet(s).”` with `vbInformation` and title `”Done”`.

—

## STEP 3 — Private Sub `PrepareColumnA(wb As Workbook, sidebarWidthPt As Double)`

**Variables to declare:**
“`vb
Dim ws As Worksheet
Dim targetChars As Double
“`

**Logic:**

Loop `For Each ws In wb.Worksheets`:

1. Call the safe helper `HasSidebarTag(ws.Cells(1, 1))`. If it returns `False` (i.e., `If Not HasSidebarTag(…)`):
a. Call `IsSidebarColumn(ws)`. If it returns `False`, insert a column: `ws.Columns(“A:A”).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove`
b. Tag cell A1 with a comment so future runs recognise the sidebar column:
– `On Error Resume Next`
– `ws.Cells(1, 1).Comment.Delete`
– `ws.Cells(1, 1).AddComment “SB_SIDEBAR”`
– `ws.Cells(1, 1).Comment.Visible = False`
– `On Error GoTo 0`
2. Outside the `If` block (always runs): call `SetColumnWidthInPoints ws.Columns(“A”), sidebarWidthPt`

—

## STEP 4 — Private Function `IsSidebarColumn(ws As Worksheet) As Boolean`

Single line body:
“`vb
IsSidebarColumn = HasSidebarTag(ws.Cells(1, 1))
“`

—

## STEP 5 — Private Function `HasSidebarTag(cell As Range) As Boolean`

**Critical safety function.** VBA does not short-circuit `Or`/`And` on object properties, so `.Comment.Text` must never be accessed in the same `If` expression as `Is Nothing`.

“`vb
HasSidebarTag = False
If cell.Comment Is Nothing Then Exit Function
If InStr(cell.Comment.Text, “SB_SIDEBAR”) > 0 Then HasSidebarTag = True
“`

—

## STEP 6 — Private Sub `SetColumnWidthInPoints(col As Range, widthPt As Double)`

Uses a **binary search loop** to converge column width (in Excel’s character-unit `ColumnWidth`) to match the desired `widthPt` in points. Excel’s `col.Width` property returns the actual width in points (read-only), while `col.ColumnWidth` is settable but in character units — hence the loop.

**Variables:**
“`vb
Dim lo As Double, hi As Double, mid As Double
lo = 1: hi = 100
“`

**Loop:**
“`vb
Do While (hi – lo) > 0.01
mid = (lo + hi) / 2
col.ColumnWidth = mid
If col.Width < widthPt Then
lo = mid
Else
hi = mid
End If
Loop
col.ColumnWidth = mid
“`

—

## STEP 7 — Private Sub `BuildSidebarOnSheet(ws As Worksheet, wb As Workbook, sidebarWPt As Double, sidebarHPt As Double)`

Orchestrates drawing all sidebar elements on one sheet. Declare geometry variables inline (using `:` colon syntax on the same line as `Dim`):

“`vb
Dim leftPt As Double: leftPt = SIDEBAR_LEFT_INCHES * 72
Dim topPt As Double: topPt = SIDEBAR_TOP_INCHES * 72
Dim btnW As Double: btnW = sidebarWPt * (1 – 2 * BTN_PADDING_X_FRAC)
Dim btnXPt As Double: btnXPt = leftPt + sidebarWPt * BTN_PADDING_X_FRAC
“`

**Call order** (must be exactly this sequence):
1. `DeleteSidebarShapes ws`
2. `DrawBackground ws, leftPt, topPt, sidebarWPt, sidebarHPt`
3. `Dim afterLogoPt As Double` → `afterLogoPt = DrawLogoBlock(ws, leftPt, topPt, sidebarWPt, LOGO_H_PT)`
4. `Dim afterDivPt As Double` → `afterDivPt = DrawDivider(ws, leftPt, afterLogoPt, sidebarWPt)`
5. `DrawNavButtons ws, wb, btnXPt, afterDivPt, btnW, BTN_H_PT, BTN_GAP_PT`

—

## STEP 8 — Private Sub `DeleteSidebarShapes(ws As Worksheet)`

**Purpose:** Safely delete all sidebar shapes. Shapes cannot be deleted while iterating the `Shapes` collection — so collect names first, then delete.

**Variables:**
“`vb
Dim shp As Shape
Dim namesToDelete() As String
Dim count As Integer: count = 0
Dim i As Integer
“`

**Loop 1** — collect names:
“`vb
For Each shp In ws.Shapes
If IsSidebarShape(shp.Name) Then
ReDim Preserve namesToDelete(count)
namesToDelete(count) = shp.Name
count = count + 1
End If
Next shp
“`

**Loop 2** — delete by name:
“`vb
For i = 0 To count – 1
On Error Resume Next
ws.Shapes(namesToDelete(i)).Delete
On Error GoTo 0
Next i
“`

—

## STEP 9 — Private Function `IsSidebarShape(shapeName As String) As Boolean`

Single line body — checks whether the shape name starts with `”SB_”`:
“`vb
IsSidebarShape = (Left(shapeName, 3) = “SB_”)
“`

—

## STEP 10 — Private Sub `DrawBackground(ws As Worksheet, L As Double, T As Double, W As Double, H As Double)`

Draws the main sidebar panel as a rounded rectangle.

“`vb
Dim shp As Shape
Set shp = ws.Shapes.AddShape(msoShapeRoundedRectangle, L, T, W, H)
shp.Name = PREFIX_BG & ws.Name
shp.Adjustments(1) = 0.03
“`

Fill block:
“`vb
With shp.Fill
.Solid
.ForeColor.RGB = RGB(18, 52, 86)
.Transparency = 0
End With
“`

Line block:
“`vb
With shp.Line
.Visible = msoFalse
End With
“`

Send to back:
“`vb
shp.ZOrder msoSendToBack
“`

—

## STEP 11 — Private Function `DrawLogoBlock(ws As Worksheet, L As Double, T As Double, W As Double, H As Double) As Double`

Draws the header title strip. Uses `msoShapeRectangle` (not rounded).

“`vb
Dim shp As Shape
Set shp = ws.Shapes.AddShape(msoShapeRectangle, L, T, W, H)
shp.Name = PREFIX_LOGO & ws.Name
“`

Fill: `RGB(10, 36, 61)`, `Transparency = 0`. Line: `msoFalse`.

TextFrame2 block — exact values:
“`vb
With shp.TextFrame2
.TextRange.Text = ChrW(9776) & ” Navigator”
.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
.TextRange.Font.Size = FONT_SIZE_LOGO
.TextRange.Font.Name = FONT_NAME
.TextRange.Font.Bold = msoTrue
.VerticalAnchor = msoAnchorMiddle
.TextRange.ParagraphFormat.Alignment = msoAlignLeft
.MarginLeft = W * BTN_PADDING_X_FRAC
End With
“`

> `ChrW(9776)` produces the ☰ hamburger icon character.

Return value:
“`vb
DrawLogoBlock = T + H
“`

—

## STEP 12 — Private Function `DrawDivider(ws As Worksheet, L As Double, T As Double, W As Double) As Double`

Draws a thin horizontal separator bar.

“`vb
Dim shp As Shape
Dim divH As Double: divH = 1.5
Dim inset As Double: inset = W * BTN_PADDING_X_FRAC

Set shp = ws.Shapes.AddShape(msoShapeRectangle, L + inset, T + 5, W – 2 * inset, divH)
shp.Name = PREFIX_DIVIDER & ws.Name
“`

Fill: `RGB(70, 130, 180)`, `Transparency = 0.4`. Line: `msoFalse`.

Return value:
“`vb
DrawDivider = T + 5 + divH + LOGO_GAP_PT
“`

—

## STEP 13 — Private Sub `DrawNavButtons(…)`

Full signature:
“`vb
Private Sub DrawNavButtons(ws As Worksheet, wb As Workbook, _
btnX As Double, startY As Double, _
btnW As Double, btnH As Double, gap As Double)
“`

**Variables:**
“`vb
Dim targetWs As Worksheet
Dim shp As Shape
Dim btnY As Double
Dim idx As Integer
Dim isActive As Boolean
btnY = startY
“`

**Loop** `For idx = 1 To wb.Worksheets.Count`:

1. `Set targetWs = wb.Worksheets(idx)`
2. `isActive = (targetWs.Name = ws.Name)`
3. Create shape:
“`vb
Set shp = ws.Shapes.AddShape(msoShapeRoundedRectangle, btnX, btnY, btnW, btnH)
shp.Name = PREFIX_BTN & idx & “_” & ws.Name
shp.Adjustments(1) = 0.18
“`
4. Fill colour — conditional on `isActive`:
– Active: `RGB(41, 182, 246)` (sky blue)
– Inactive: `RGB(25, 80, 130)` (muted blue)
– `Transparency = 0` in both cases
5. Line: `msoFalse`
6. Text — declare `Dim icon As String` **inside the loop** (VBA allows this):
– Active: `icon = ChrW(9654) & ” “` (▶ arrow)
– Inactive: `icon = ” “` (four spaces indent)
– `.TextRange.Text = icon & targetWs.Name`
– Font colour: `RGB(255, 255, 255)`, Size: `FONT_SIZE_BTN`, Name: `FONT_NAME`
– Bold: `msoTrue` if active, `msoFalse` if not
– `VerticalAnchor = msoAnchorMiddle`
– `ParagraphFormat.Alignment = msoAlignLeft`
– `MarginLeft = 8`
– `WordWrap = msoFalse`
7. Add hyperlink — **no `OnAction` macro**:
“`vb
ws.Hyperlinks.Add _
Anchor:=shp, _
Address:=””, _
SubAddress:=”‘” & targetWs.Name & “‘!A1″, _
ScreenTip:=”Go to ” & targetWs.Name
“`
8. Advance: `btnY = btnY + btnH + gap`

`Next idx`

> The hyperlink `SubAddress` wraps the sheet name in single quotes so sheet names containing spaces work correctly. `Address:=””` keeps the link internal (no URL).

—

## STEP 14 — Public Sub `RemoveSidebar()`

Utility macro to completely uninstall the sidebar from all sheets.

**Variables:**
“`vb
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Application.ScreenUpdating = False
Application.EnableEvents = False
“`

**Loop** `For Each ws In wb.Worksheets`:
1. `DeleteSidebarShapes ws`
2. Use `HasSidebarTag(ws.Cells(1, 1))` to safely check before touching `.Comment.Text`:
“`vb
If HasSidebarTag(ws.Cells(1, 1)) Then
ws.Cells(1, 1).Comment.Delete
ws.Columns(“A:A”).Delete
End If
“`

After loop:
“`vb
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox “Sidebar removed from all sheets.”, vbInformation
“`

—

## PROCEDURE ORDER SUMMARY

The procedures must appear in this exact top-to-bottom order in the module:

| # | Name | Type | Visibility |
|—|—|—|—|
| 1 | `CreateSidebar` | Sub | Public |
| 2 | `PrepareColumnA` | Sub | Private |
| 3 | `IsSidebarColumn` | Function | Private |
| 4 | `HasSidebarTag` | Function | Private |
| 5 | `SetColumnWidthInPoints` | Sub | Private |
| 6 | `BuildSidebarOnSheet` | Sub | Private |
| 7 | `DeleteSidebarShapes` | Sub | Private |
| 8 | `IsSidebarShape` | Function | Private |
| 9 | `DrawBackground` | Sub | Private |
| 10 | `DrawLogoBlock` | Function | Private |
| 11 | `DrawDivider` | Function | Private |
| 12 | `DrawNavButtons` | Sub | Private |
| 13 | `RemoveSidebar` | Sub | Public |

—

## KEY RULES & CONSTRAINTS

1. **No `OnAction`** — navigation uses `ws.Hyperlinks.Add` only. No `NavTo` sub exists.
2. **No ActiveX controls** — pure Shapes API only.
3. **No hard-coded sheet names** — always loop through `wb.Worksheets` dynamically.
4. **Safe re-runs** — `DeleteSidebarShapes` clears all `”SB_”` prefixed shapes before rebuilding. The column-A comment tag `”SB_SIDEBAR”` prevents double-insertion.
5. **Safe comment access** — never write `.Comment.Text` and `.Comment Is Nothing` in the same `If` condition. Always use `HasSidebarTag()` which checks existence on a separate line first.
6. **Binary search for column width** — `SetColumnWidthInPoints` iterates `lo`/`hi`/`mid` until `(hi – lo) > 0.01` to match points precisely, because `ColumnWidth` uses character units not points.
7. **`ChrW()` not `Chr()`** for Unicode characters — `ChrW(9776)` = ☰, `ChrW(9654)` = ▶.
8. **Collect-then-delete pattern** — `DeleteSidebarShapes` uses a two-loop approach (collect names into an array, then delete) because deleting shapes while iterating the Shapes collection causes errors.
9. **`ZOrder msoSendToBack`** — the background panel is explicitly sent behind all other shapes after creation.
10. **`Dim` inside loop** — `Dim icon As String` is declared inside the `For` loop in `DrawNavButtons`. This is valid in VBA (scope is the procedure, not the block).

You can find the Prompt file from the below link.

Related Posts

Leave a Reply Cancel Reply