Hi all, I have 2 sheets in a workbook. One is a Covering Letter, and the other is a Quotation. The quotation has various options, with one being Branch (for different branch offices) where a drop-down list allows selecting the branch. Each branch has its unique letterhead stored as images in a separate desktop folder. Currently, the letterhead logo is pasted in the Header section of the workbook. Is it possible for Excel to change the letterhead in the Header section to match the selected branch from the drop-down list?
Is this feasible, or is it an unusual and impossible request? Your help is greatly appreciated.
From United Arab Emirates, Dubai
Acknowledge(0)
Amend(0)
Is this feasible, or is it an unusual and impossible request? Your help is greatly appreciated.
From United Arab Emirates, Dubai
Acknowledge(0)Amend(0)
To dynamically change the letterhead in Excel based on the selected branch, you can utilize Excel VBA (Visual Basic for Applications) to automate this process. Here's a step-by-step guide to achieve this:
1. Prepare Letterhead Images: Ensure you have all branch letterhead images saved in a specific folder on your desktop.
2. Access VBA Editor: Press `Alt + F11` to open the VBA editor.
3. Insert VBA Code: Go to `Insert > Module` and paste the following VBA code:
```vba
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Dim Branch As String
Branch = Target.Value
Select Case Branch
Case "Branch1"
ThisWorkbook.Sheets("Covering Letter").PageSetup.LeftHeaderPicture.Filename = "C:\Path\To\Branch1\Letterhead.jpg"
Case "Branch2"
ThisWorkbook.Sheets("Covering Letter").PageSetup.LeftHeaderPicture.Filename = "C:\Path\To\Branch2\Letterhead.jpg"
' Add more cases for each branch
End Select
End If
End Sub
```
4. Modify Code: Update the branch names and file paths in the VBA code to match your setup.
5. Test the Functionality: Save the workbook as a macro-enabled file and test changing the branch selection to see the letterhead update accordingly.
By following these steps, you can automate the process of changing the letterhead in Excel based on the selected branch, enhancing the professional presentation of your documents.
From India, Gurugram
Acknowledge(0)Amend(0)
1391. Prepare Letterhead Images: Ensure you have all branch letterhead images saved in a specific folder on your desktop.
2. Access VBA Editor: Press `Alt + F11` to open the VBA editor.
3. Insert VBA Code: Go to `Insert > Module` and paste the following VBA code:
```vba
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Dim Branch As String
Branch = Target.Value
Select Case Branch
Case "Branch1"
ThisWorkbook.Sheets("Covering Letter").PageSetup.LeftHeaderPicture.Filename = "C:\Path\To\Branch1\Letterhead.jpg"
Case "Branch2"
ThisWorkbook.Sheets("Covering Letter").PageSetup.LeftHeaderPicture.Filename = "C:\Path\To\Branch2\Letterhead.jpg"
' Add more cases for each branch
End Select
End If
End Sub
```
4. Modify Code: Update the branch names and file paths in the VBA code to match your setup.
5. Test the Functionality: Save the workbook as a macro-enabled file and test changing the branch selection to see the letterhead update accordingly.
By following these steps, you can automate the process of changing the letterhead in Excel based on the selected branch, enhancing the professional presentation of your documents.
From India, Gurugram
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
MAIL MERGE AND LABELING.docx
Advanced Excel 2003.pdf
Competancy--exc el---mts___smts _289.xls