+ Reply to Thread
Results 1 to 2 of 2

Random background image from folder with 1 error

  1. #1
    Registered User
    Join Date
    02-08-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    80

    Random background image from folder with 1 error

    Hello,

    I made a background change on loop from a folder with pictures.
    The macro starts as I open the xlsm.
    The background changes every xx second and works perfectly, outside one error.

    It sometimes (more frequently if less images in the folder) displays a white background, as if there was a plain white picture in the folder.
    I even made the test to only keep 1 image in the folder and it shows the image then plain white, as background.

    Any idea why?

    ThisWorkbook:
    Private Sub Workbook_Open()
    Call file_names
    Call Macro1
    Application.WindowState = xlNormal
    Application.Top = 0
    Application.Left = 815
    Application.Width = 627
    Application.Height = 782
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call Macro2
    End Sub


    Module1:
    Option Explicit

    Const pth = "D:\Backgrounds"
    Const mfPttrn = "*.jpg"

    Private i As Integer, rn As Integer
    Private TimeToRun As Date
    Private fleTbl() As String

    Sub file_names()
    Dim fle As String
    i = CreateObject("Scripting.FileSystemObject").GetFolder(pth).Files.Count
    ReDim fleTbl(1 To i, 1 To 2)
    i = 0
    fle = Dir(pth & mfPttrn, vbNormal)
    Do Until fle = ""
    i = i + 1
    fleTbl(i, 1) = i
    fleTbl(i, 2) = pth & fle
    fle = Dir()
    Loop
    End Sub
    Sub Macro1()
    Randomize
    rn = Int(UBound(fleTbl, 1) * Rnd + 1)
    ActiveSheet.SetBackgroundPicture Filename:=fleTbl(rn, 2)
    TimeToRun = Now + TimeValue("00:00:30")
    'Change duration above hours:minutes:seconds
    Application.OnTime EarliestTime:=TimeToRun, Procedure:="Macro1"

    End Sub

    Public Function FilenameFromPath(path As String) As String

    Dim S As String
    Dim V As Variant

    ' Make sure all separators are the same
    path = Replace(path, "/", "")

    V = Split(path, "")
    FilenameFromPath = V(UBound(V))

    End Function

    Sub Macro2()
    On Error Resume Next
    Application.OnTime EarliestTime:=TimeToRun, Procedure:="Macro1", Schedule:=False
    ActiveSheet.SetBackgroundPicture Filename:=""
    fleTbl = Empty
    End Sub

  2. #2
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit - Win 11
    Posts
    928

    Re: Random background image from folder with 1 error

    I tried to do some debugging; is there any chance that there is one (or more) non .jpg files in the folder ? It's the only case where I randomly get a white background.
    In such case with this line:
    Please Login or Register  to view this content.
    you then redim your array bigger than necessary giving you blank elements.



    Ps. by the way, shouldn't this line have a trailing backslash ?
    Please Login or Register  to view this content.
    Last edited by rollis13; 02-14-2023 at 06:38 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to change the background image and copy the image filename into a cell
    By Zenarte in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-27-2022, 02:51 PM
  2. Error in code to save image in user form in a specific folder
    By ss1590 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-13-2019, 10:08 AM
  3. Vlookup into an image folder and importing the image into the spreadsheet?
    By talan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-30-2017, 12:54 PM
  4. How to set Image in Background using URL
    By megel57 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-04-2013, 02:19 PM
  5. Image in the background
    By pdhawan in forum Excel General
    Replies: 3
    Last Post: 11-25-2011, 03:16 AM
  6. Background image
    By kenelder in forum Excel General
    Replies: 1
    Last Post: 10-31-2008, 11:50 AM
  7. Background image
    By beesus311 in forum Excel General
    Replies: 1
    Last Post: 02-23-2008, 10:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1