+ Reply to Thread
Results 1 to 8 of 8

Run a Macro on a sheet from a button on another sheet

  1. #1
    Registered User
    Join Date
    06-23-2022
    Location
    Cumbria
    MS-Off Ver
    10
    Posts
    5

    Run a Macro on a sheet from a button on another sheet

    HI ,

    I have a macro which gets folder names from a directory using a folder path on sheet 2, the data then populates sheet 2 with all the folder names in that directory path using the patch entered into cell F1. I am looking to place a button on sheet 1 and have it run the macro in sheet 2, i have tried the call macro method but it doesn't work.

    This is my VBA code for the sheet 2 macro :

    Function DirList(SCAN$, Optional FOLD$, Optional ATTR As VbFileAttribute = vbNormal) As String()
    Dim B%, D$, F$, T$(), U&
    With Application
    If FOLD > "" Then
    If Right(FOLD, 1) <> .PathSeparator And Left(SCAN, 1) <> .PathSeparator Then FOLD = FOLD & .PathSeparator
    D = FOLD
    Else
    D = Left$(SCAN, InStrRev(SCAN, .PathSeparator))
    End If
    End With
    If SCAN = "." Then SCAN = "*."
    On Error Resume Next
    F = Dir(FOLD & SCAN, ATTR)
    Do Until F = ""
    If ATTR And vbDirectory Then B = Right(F, 1) = "." Or (GetAttr(D & F) And vbDirectory) = 0
    If B = 0 Then U = U + 1: ReDim Preserve T(1 To U): T(U) = FOLD & F
    F = Dir
    Loop
    DirList = IIf(U, T, Split(""))
    End Function

    Sub Demo1()
    Dim S$()
    If Not IsEmpty([f1]) Then
    S = DirList([f1&IF(RIGHT(b1,1)="\","","\")], , 16)
    [f3].Resize(UBound(S)).Value2 = Application.Transpose(S)
    End If
    End Sub

    The macros are stored in modules rather than the individual sheets (i have tried that method as well to no avail)

    Any advice on how to accomplish this either with another macro in Sheet 1 to run this one or any other method would be welcome.

    Thank You

  2. #2
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    646

    Re: Run a Macro on a sheet from a button on another sheet

    Maybe put a button on sheet1 and assign it to the below macro:
    Please Login or Register  to view this content.
    When you use [f3] it is in reference to the active sheet, if you define the ranges with the sheet then it will look at the values on sheet2 but run from sheet1.

    You may need to edit what i have done with the \\\\\\'s
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  3. #3
    Registered User
    Join Date
    06-23-2022
    Location
    Cumbria
    MS-Off Ver
    10
    Posts
    5

    Re: Run a Macro on a sheet from a button on another sheet

    Thank you for the response, I am getting a compile error Argument not optional - Str = rCell.Value & "\" when I try to run the macro

  4. #4
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    646

    Re: Run a Macro on a sheet from a button on another sheet

    Sorry, I pasted the wrong sub, complete one below:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-23-2022
    Location
    Cumbria
    MS-Off Ver
    10
    Posts
    5

    Re: Run a Macro on a sheet from a button on another sheet

    Thanks for the update, it still doesnt update, am i doing something wrong ? I put the code into a module instead of the sheet, and created a button on sheet 1 with the macro assigned. I have renamed the above macro Demo11 as the initial macro ID called Demo1.

    It just doesnt seem to run the other macro (Demo1). Cheers

  6. #6
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    646

    Re: Run a Macro on a sheet from a button on another sheet

    At the moment it should be spilling the results on sheet2, are there any results on sheet2?

    Might be good to step through the code line by line using the F8 key, you can eye up the values the code is creating like the file path etc...

  7. #7
    Registered User
    Join Date
    06-23-2022
    Location
    Cumbria
    MS-Off Ver
    10
    Posts
    5

    Re: Run a Macro on a sheet from a button on another sheet

    No results currently on sheet 2

    The results should start filling in cell f3 down the f column until all are displayed

    The Directory path is in F1 and is accessible


    The macro you have supplied is linked to button in sheet 1 - I have done the f8 test on both macros but nothing happens - can the macro you supplied be linked directly to the one I have originally or should it do it automatically?

  8. #8
    Registered User
    Join Date
    06-23-2022
    Location
    Cumbria
    MS-Off Ver
    10
    Posts
    5

    Re: Run a Macro on a sheet from a button on another sheet

    Right managed to get it working, I was using the modules in the personal VBA project instead of in the actual workbook, the only problem I am encountering now is that when I run the macro you designed it is clearing cell F1 in sheet 2 and starts listing the folders there instead of starting at f3, also in the original macro It says B1 :

    S = DirList([f1&IF(RIGHT(b1,1)="\","","\")], , 16)

    however nothing should relate to b1 as there is nothing there, I think that should say f1 but not sure as themacro was from another query originally.

    Any advice on how to get the list to start at F3 on Sheet 2 when running your macro would be great and also if you could advise if I change B1 to F1.

    My Data in Sheet 2 : F1 contains the file path to draw the information from
    F2 Is a message to the user to ensure they have access to the folder before commencing with the macro
    F3 is the start point for the filling data down column F of the folders
    F1 and F2 must be unaffected by the new macro

    Cheers

+ 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. Needing a Macro Button to execute Subtraction on Sheet 1 and Addition on Sheet 2
    By LeUnknown in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-17-2020, 04:19 AM
  2. [SOLVED] Data send one sheet to another sheet based on pressing the macro button
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2017, 11:20 AM
  3. Replies: 1
    Last Post: 05-12-2016, 03:36 AM
  4. Replies: 2
    Last Post: 05-12-2016, 03:08 AM
  5. Macro to find sheet name with next button option in case of multiple sheet with same name.
    By DEEPAK AGGARWAL in forum Excel Programming / VBA / Macros
    Replies: 38
    Last Post: 05-01-2016, 12:27 PM
  6. Excel Macro - Filter button activate on both sheet if activated on one sheet
    By umeshbanga in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-26-2014, 02:22 PM
  7. Replies: 0
    Last Post: 03-13-2013, 03:14 PM

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