Different Background each time user opens spread sheet

[Malcolm torishi]

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

 

[DanteAmor]

Put the following code in the events of your sheet

Change data in red by your information

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]

Dante
what can I say you have answered my question again and come up with the answer. Thank you very much

 

[DanteAmor]

Dante
what can I say you have answered my question again and come up with the answer. Thank you very much

I'm glad to help you. Thanks for the feedback.

 

[Malcolm torishi]

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]

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]

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]

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]

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]

I'm glad to help you. Thanks for the feedback.