Use a drop down list to select company info in header (vba)
I have a spreadsheet that I use for 3 different companies.
What i would really like to do is have a drop down menu with the three company names: eg: Mcdonalds, Pizza Hut, Subway and then when i choose which company the spreadsheet will be for then all the contact information and logo will appear as a header on the top of the spread sheet. is this possible?
Answer:
Sheet1:
VBA code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Single
If Not Intersect(Target, Range("C2")) Is Nothing Then
On Error Resume Next
iRow = Application.Match(Range("c2"), Worksheets("Data").Range("A1:A4").Value, 0)
If Err <> 0 Then
MsgBox "Company name not found!"
Exit Sub
End If
On Error GoTo 0
With ActiveSheet
.PageSetup.LeftHeader = Worksheets("Data").Range("A" & iRow).Text & Chr(10) & _
Worksheets("Data").Range("B" & iRow).Text
.PageSetup.CenterHeader = Worksheets("Data").Range("C" & iRow).Text & " " & _
Worksheets("Data").Range("D" & iRow).Text & _
Chr(10) & Worksheets("Data").Range("E" & iRow).Text
.PageSetup.RightHeaderPicture.Filename = Worksheets("Data").Range("G" & iRow).Text
End With
End If
End Sub
Copy vba code and paste in sheet module
- Copy code
- Right click on sheet name
- Click "View code"
- Paste code into sheet module
- Exit vba editor
Sheet "Data":
Column F contains folder paths and file names to company logos.
Download excel *.xlsm file
Recommended posts
Use a drop down list to extract and concatenate unique distinct values
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Create a drop down list containing only unique distinct alphabetically sorted text values
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
How to use a Table name in Data Validation Lists and Conditional Formatting formulas
David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]
Create a drop down list containing alphabetically sorted values
Overview This article describes how to create a drop down list populated with sorted values from A to Z. The […]8 Responses to “Use a drop down list to select company info in header (vba)”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
){kind=link}
Personally I like to use VLOOKUP or INDEX/MATCH for this, but this works when you already have a workbook with VBA in it.
JP,
Yes, as far as I know I can´t use formulas in header or footer. I have to use vba.
Yes, sorry, I forgot to mention that. Obviously you would need VBA to adjust the header/footer.
I find that the picture isn't showing up
Actually I find that none of it is working, can you put more detailed instructions?
Aynsley,
I find that the picture isn't showing up.
You have to change folder paths and file names in sheet "Data" to your own saved images.
I tried that, still didn't work. No pictures - No change - Nothing worked.
Aynsley,
Do you have the exact folderpaths and filenames?
Do you use png or jpg files? Not that it matters, I think. But I am out of ideas.