Attend Excelapalooza
Likes Likes:  0
Results 1 to 8 of 8

Thread: Calling A Function From Button

  1. #1
    Board Regular
    Join Date
    Oct 2016
    Location
    Midwest, USA
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calling A Function From Button

    I know (hope) this is a simple problem and I am just overlooking it today.

    I am simply calling a function (Gen30Day) from a button (btnGen30) click (_Click).

    I cannot see why this is erring. I'm relatively new to VBA, but not programming. I feel like this is either a very simple mistake or just I can't see anything.

    Sub btnGen30_Click() Call Gen30Day
    End Sub

    Public Sub Gen30Day()
    'STUFF
    End Sub

  2. #2
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,369
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Simply Calling A Function From Button - Second Set of Eyes Please

    On the assumption that Call Gen30Day isn't actually on the same line as Sub btnGen30_Click(), there's nothing wrong with your code.

    What's the error message/

  3. #3
    Board Regular
    Join Date
    Jul 2017
    Posts
    264
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simply Calling A Function From Button - Second Set of Eyes Please

    Hi

    Is this an ActiveX button for which the code is in the sheet module?

    I created a button with the same name and created a click event in the sheet module.

    Code:
    Option Explicit
    
    
    Private Sub btnGen30_Click()
    Gen30Day
    End Sub
    I then created a snippet of code to run called Gen30Day in a normal module, in order to keep it out the macro dialog box I set the module to private.

    Code:
    Option Private Module
    
    
    Sub Gen30Day()
    MsgBox "Gen30Day running fine"
    End Sub
    This seemed to work, incidentally you shouldn't need to use the Call keyword.

    HTH
    Excel 2016 on Windows 10

    Please use [code][/code] tags

  4. #4
    Board Regular
    Join Date
    Oct 2016
    Location
    Midwest, USA
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simply Calling A Function From Button - Second Set of Eyes Please

    No, my apologies, that was a formatting issue on my paste. Thanks for letting me know it looks legit!
    Last edited by JPARKHURST; Dec 6th, 2017 at 11:32 AM.

  5. #5
    Board Regular
    Join Date
    Oct 2016
    Location
    Midwest, USA
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simply Calling A Function From Button - Second Set of Eyes Please

    If you don't mind, I have a few [very ignorant] questions. Still learning the framework I'm working in. Also, just to be clear, the code itself (gen30) is working when I play it inside the scripting window.

    A quick bit: I have some background in web development, but it has been years since I practiced it (3-4). I am currently working in production control and have been asked to automate many of our schedules. This is the first real step toward that end.

    Quote Originally Posted by mrhstn View Post
    Hi

    Is this an ActiveX button for which the code is in the sheet module?

    Yes. I have a Sheet5(Dashboard) where this button and the code [for the button] are located

    I created a button with the same name and created a click event in the sheet module.

    Code:
    Option Explicit
    
    
    Private Sub btnGen30_Click()
    Gen30Day
    End Sub
    I then created a snippet of code to run called Gen30Day in a normal module, in order to keep it out the macro dialog box I set the module to private.

    OK, here I have some questions or problems. In order to attempt to duplicate your trial, I opened my sheet.


    My problem was discovered when I put Option Private Module on the top line. When I typed that in, it told me Compile Error: Option Private Module not permitted in an object module.

    When I remove the module, it requests I put it back in. *very* helpful. This is where I came to the conclusion below.

    On the worksheet(Code) for the Sub Gen30Day(), I have (General) in the top left most blank, showing the module I am working on in the top right. Is this incorrect? I was hoping to put all of my code in here eventually to re-use some of it. But I'm kinda guessing the code will need to go..where - attach to a worksheet itself?

    Code:
    Option Private Module
    
    
    Sub Gen30Day()
    MsgBox "Gen30Day running fine"
    End Sub
    This seemed to work, incidentally you shouldn't need to use the Call keyword.

    HTH
    =========================All of Code===================
    This is still being written, so some of this may need to be eliminated, properly closed out, etc. I can fix those errors, but am also completely open to criticism if you see me going down the wrong path. Some of the snippets I toyed with using, then disposed of as I found better, faster or shorter ways to solve issues. I have not re-sued all of them (but will be taking them out as I continue to search for solutions to this issue. I am simply copying it in here for transparency.

    This is in the sheet ThisWorkbook, and again top right drop down has (General)

    Public Function Range_End(ws As String) As Long
    Dim lRow As Long
    Dim lCol As Long

    Sheets(ws).Activate
    lRow = Cells.Find(What:="*", _
    After:=Range("A1"), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
    Range_End = lRow

    End Function
    Sub CreateSheet(ByVal strSheetName As String, Optional ByVal rngHeader As Range)
    Dim wsTest As Worksheet
    Set wsTest = Nothing
    On Error Resume Next

    Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
    On Error GoTo 0

    If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
    'MsgBox "Sheet " & strSheetName & " created."
    End If
    End Sub


    Sub Erase30(ws1 As Worksheet, rngClear As Range)
    'Remove Content
    ws1.Select
    rngClear.Clear


    End Sub
    Public Sub Gen30Day()


    'WORKBOOKS
    Dim wbCurrent As Workbook
    'WORKSHEETS
    Dim wsOrigin As Worksheet
    Set wsOrigin = ActiveSheet
    Dim wsData As Worksheet 'Name Worksheet gathering data from
    Dim wsCopyTo As Worksheet 'Used for the new worksheet we are pasting into

    Dim dtBeginDate As Date


    Dim rngBurnDown As Range
    Dim rngCell As Range
    Dim rngNextAvailbleRow As Range
    Dim rngClearData As Range


    Dim c As Long
    c = 1
    Dim nClearDataLastRow As Long

    Dim wsName As String
    Dim stRev As String
    Dim stRevChk As String
    Dim stClearData As String

    stRevChk = "Original"


    dtToday = Date 'ERROR CHECKING MsgBox "dtToday: " & dtToday
    dtBeginDate = DateAdd("d", -31, dtToday) 'ERROR CHECKING MsgBox "dtBeginDate: " & dtBeginDate


    Set wbCurrent = ActiveWorkbook
    Set wsData = Sheets("Data 2017") 'Define the worksheet with our data


    wsName = "Data 30 Day"
    'Now identify and select the new sheet to paste into
    Set wsCopyTo = wbCurrent.Worksheets(wsName)




    'Determine last row of Data in CopyTo & Create reference String to Create Dynamic Range
    nClearDataLastRow = Range_End(wsName)
    stClearData = "A3:I" & nClearDataLastRow
    Set rngClearData = wsCopyTo.Range(stClearData)
    Call Erase30(wsCopyTo, rngClearData)


    'Dynamically define the range to the last cell.
    'If we are not starting in A3, then change as appropriate
    Set rngBurnDown = wsData.Range("A3:A" & wsData.Cells(Rows.Count, "A").End(xlUp).Row)

    'Now loop through all the cells in the range
    For Each rngCell In rngBurnDown.Cells
    wsData.Select
    'determine if date is appropriate

    If rngCell.Value <> "" Then
    If rngCell.Value >= dtBeginDate Then
    If rngCell.Value <= dtToday Then
    rngCell.EntireRow.Select
    Selection.Copy
    wsCopyTo.Activate 'changed from .select to .activate
    If rngCell.Cells(c, 4).Value = stRevChk Then
    Set rngNextAvailbleRow = wsCopyTo.Range("A1:A" & wsCopyTo.Cells(Rows.Count, "A").End(xlUp).Row)
    Range("A" & rngNextAvailbleRow.Rows.Count + 1).Select
    Worksheets(wsName).Paste 'jcp edit
    End If 'end of version check
    End If 'end if less than or today
    End If 'End End Date If Statement
    End If 'End Begin Date If Statement
    Next rngCell


    wsData.Select
    wsData.Cells(1, 1).Select


    'Can do some basic error handing here


    'kill all objects
    If IsObject(wbCurrent) Then Set wbCurrent = Nothing
    If IsObject(wsData) Then Set wsData = Nothing
    If IsObject(wsCopyTo) Then Set wsCopyTo = Nothing
    If IsObject(rngBurnDown) Then Set rngBurnDown = Nothing
    If IsObject(rngCell) Then Set rngCell = Nothing
    If IsObject(rngNextAvailbleRow) Then Set rngNextAvailbleRow = Nothing

    'begin where we end
    wsOrigin.Activate
    End Sub


    Private Sub Workbook_Open()


    End Sub

  6. #6
    Board Regular
    Join Date
    Oct 2016
    Location
    Midwest, USA
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simply Calling A Function From Button - Second Set of Eyes Please

    OK, something, somewhere is fixed. I cleaned up my code, tweaked a few things, went through each line and .... It must have been in the button generation/macro assignment, I guess, as it was my last action before a successful test.

    Thank you all for your help, always have a great response on here. I look forward to the day I will have some experience to give back.

    Thanks again,

    Jon

  7. #7
    Board Regular
    Join Date
    Jul 2017
    Posts
    264
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simply Calling A Function From Button - Second Set of Eyes Please

    Hi Jon

    Sorry for the delayed reply, business called. Glad you got it working.

    For the record, Option Private Module wouldn't work in the sheet module, only a standard module (Insert>>Module). I only included it to keep the macros out of the run macro dialog box, in this instance it would be your choice whether that was necessary or not.

    As you are putting both button code and the code to execute in the same module you can call both Private Sub they will run when the event calls them.

    Like I say, glad you got it going.

    Regards
    Excel 2016 on Windows 10

    Please use [code][/code] tags

  8. #8
    Board Regular
    Join Date
    Oct 2016
    Location
    Midwest, USA
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simply Calling A Function From Button - Second Set of Eyes Please

    I appreciate the explanation, still getting used to the environment of Excel. I wish i had a little more prep time before this project needed to be completed, but...

    Again, thanks!

    Jon

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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