Different Background each time user opens spread sheet

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
Hi
can anyone tell me if it’s possible to have a different background say on sheet 1 when a user opens up a spread sheet. I assume it may be a VBA Code that can do this . If anyone knows it would be great to hear from you thank you
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Put the following code in the events of your sheet

Change data in red by your information

Code:
Private Sub Worksheet_Activate()
  Dim wName As String, wPath As String, fso As Object, wFile As Object
  Dim sht As Worksheet, n As Long, m As Variant, tot As Long
  Set fso = CreateObject("scripting.FileSystemObject")
  Set sht = Sheets("Temp")
  
  wPath = "[COLOR=#ff0000]C:\Users\damor\Pictures\[/COLOR]"           '[COLOR=#008000]enter your folder path[/COLOR]
  n = 0
  m = sht.Range("A1")
  If m = "" Or Not IsNumeric(m) Then m = 1
  
  For Each wFile In fso.getfolder(wPath).Files
    Select Case fso.GetExtensionName(wFile)
      Case [COLOR=#ff0000]"jpg", "jpeg", "png"                          '[/COLOR][COLOR=#008000]enter  the extension of image files[/COLOR]
        n = n + 1
        tot = tot + 1
        If n = m Then wName = wFile.Name
    End Select
  Next
  If wName <> "" Then
    Sheets(1).SetBackgroundPicture Filename:=wPath & wName
    If m >= tot Then m = 0
    sht.Range("A1") = m + 1
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

It works this way. Select any sheet, now select sheet 1, the background changes. Each time you select sheet 1, the background will change to one of the images in your folder.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
Dante I hope you are able to pick this message up
i have inserted this code into my worksheet and changed the path to my background images, but unfortunately the code breaks at
For Each wFile In so ................. . Files
tried to resolve it but just can’t do it, do you know why it might be breaking
 
Upvote 0
Dante I hope you are able to pick this message up
i have inserted this code into my worksheet and changed the path to my background images, but unfortunately the code breaks at
For Each wFile In so ................. . Files
tried to resolve it but just can’t do it, do you know why it might be breaking

You can put the updated code.


What error message does the macro send?
 
Upvote 0
You can put the updated code.


What error message does the macro send?
I don’t think there is an error message, what happens is when I click from one worksheet back to the worksheet I want to change the background on the screen goes straight to the code in VBA and highlights in yet the line of code where it breaks . If you think there should be an error message I can look tomorrow
 
Upvote 0
I don’t think there is an error message, what happens is when I click from one worksheet back to the worksheet I want to change the background on the screen goes straight to the code in VBA and highlights in yet the line of code where it breaks . If you think there should be an error message I can look tomorrow

I'm not understanding.
Does the code work or not work?
It worked once, but you made changes to the code and now it doesn't work.
The macro stops but does not send any error messages?
Ok, when you can check it out ...
 
Upvote 0
I'm not understanding.
Does the code work or not work?
It worked once, but you made changes to the code and now it doesn't work.
The macro stops but does not send any error messages?
Ok, when you can check it out ...

Hello again Dante
i managed to get your code to work, the reason it did not yesterday was I spelt the route to the image files wrong. Thank you again I do appreciate your help
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top