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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,137
Office Version
2007
Platform
Windows
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.
 

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
Dante
what can I say you have answered my question again and come up with the answer. Thank you very much
 

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,137
Office Version
2007
Platform
Windows
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?
 

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,137
Office Version
2007
Platform
Windows
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 ...
 

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,137
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,152
Messages
5,485,058
Members
407,479
Latest member
jbone2020

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top