Macro Based on Command Button Location



  • I am creating a spreadsheet for work that will have a list of requests from team members and I have created a macro that will send an email to the requestor when it is completed.  There will be approximately 250-400 rows.  There will be a button on every row to run the macro.  I have completed the email part of the macro, but how do I reference the row based on the command button so I dont have to write 250-400 macros.

    Here is my code so far....

     Sub LotusMail()
         
         ' setting up various objects
        Dim Maildb As Object
        Dim UserName As String
        Dim MailDbName As String
        Dim MailDoc As Object
        Dim attachME As Object
        Dim Session As Object
        Dim EmbedObj1 As Object
        Dim recipient As String
        Dim ccRecipient As String
        Dim bccRecipient As String
        Dim subject As String
        Dim bodytext As String
        Dim Attachment1 As String
        Dim t1 As Range
        Dim t2 As String
        Dim t3 As Range
        Dim t4 As String
         
        Set t1 = Range("D2")
        t2 = "Your off-phone activity request for "
        Set t3 = Range("E2")
        t4 = " was completed on "
        
         ' setting up all sending recipients
        recipient = Range("C2")
        ccRecipient = ""
        bccRecipient = ""
        subject = "Excel to Lotus Test Mail"
        bodytext = "Dear "
         
         
         
         '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text
        If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then
            MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation
            Exit Sub
        End If
         
         ' creating a notes session
        Set Session = CreateObject("Notes.NotesSession")
        UserName = Session.UserName
        MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
        Set Maildb = Session.GETDATABASE("", MailDbName)
         
        If Maildb.IsOpen <> True Then
            On Error Resume Next
            Maildb.OPENMAIL
        End If
         
        Set MailDoc = Maildb.CreateDocument
        MailDoc.form = "Memo"
         
         ' loading the lotus notes e-mail with the inputed data
        With MailDoc
            .sendto = recipient
            .copyto = ccRecipient
            .blindcopyto = bccRecipient
            .subject = subject
            .body = bodytext + t1 + t2 + t3 + t4
        End With
         
         ' saving message
        MailDoc.SaveMessageOnSend = True
         
        Attachment1 = Worksheets("Data").Range("b2").Value
        If Attachment1 <> "" Then
            Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment1")
            Set EmbedObj1 = attachME.EmbedObject(1454, "", Attachment1, "Attachment")
            MailDoc.CREATERICHTEXTITEM ("Attachment")
        End If
         
         
         ' send e-mail !!!!
        MailDoc.PostedDate = Now()
         ' if error in attachment or name of recipients
        On Error GoTo errorhandler1
         
        MailDoc.Send 0, recipient
         
        Set Maildb = Nothing
        Set MailDoc = Nothing
        Set attachME = Nothing
        Set Session = Nothing
        Set EmbedObj1 = Nothing
         
         'Unload Me
        Exit Sub
         ' setting up the error message
    errorhandler1:
        MsgBox "Incorrect name supplied or the attachment has not attached," & _
        "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _
        "to ensure the application runs correctly and that a vaild connection exists"
         
        Set Maildb = Nothing
        Set MailDoc = Nothing
        Set attachME = Nothing
        Set Session = Nothing
        Set EmbedObj1 = Nothing
         ' unloading the userform
         'Unload Me
    End Sub



  • First of all, how many users will use that sheet? If the answer is more than one, find a different solution, for instance a simple web interface. I doubt whether a spreadsheet is a good solution here; even an Access db might be a better choice (and that's saying something).

    I can think of a number of solutions, but that's all VB, and I'm not sure any of it works in VBA. In general, you need to pass some parameter to the sub, but that was probably obvious; the hard part is getting that parameter, right?

    - If you have an array of buttons, the Index property of the button can give you the row
    - you could use the name of the button
    - in a pinch, you might even be able to use the caption of the button (eek)
    - if either of those work, you might be able to generate the buttons on the fly when starting up

    But again, if you need a row of buttons like that, I wonder whether you're on the right track. 

     



  •  The only problem is my resources are kind of limited.  Excel is the only tool I am able to use at work.  (My company frowns on outside applications).  There will be 20-30 users of the sheet, but I will be the only one using the macro.  I will just hide the macro and the button for it on the public file.  Also, I'm not a programmer by trade and VBA is the only language I am even slightly familiar with.  Is there a function that can check the row when the button is clicked and store it?



  • @bombtek187 said:

    There will be 20-30 users of the sheet
     

    Bad. Only one user can edit the sheet at a time. You will have problems. Promise.

    @bombtek187 said:

    Is there a function that can check the row when the button is clicked and store it?

    I gave you a few hints on what to look for, I can't solve it for you right now. To be honest, all my hints will be hard without proper programming experience; just conjuring up a macro is not going to cut it.

    Easiest way to solve it: have just one button and let the user enter a rownumber somewhere. You can reference a cell with cell(row, col) or you could probably use inputbox() to retrieve the row number.

    But I'm going to be an asshole and warn you once more; Excel is not the tool for this.

     



  • If you're going to have a button on every row, won't you have to have a [button name]_Click() event handler for each one? You can use that to call your macro with a row parameter (it should be easy enough to generate the code for that, assuming your button names are in a logical order).

    Failing that, you can access the buttons' properties through the worksheet's Shapes collection. The TopLeftCell and BottomRightCell properties seem to be about what you want.

    But really, I'd just have a single button and instruct them to make sure the active cell is in the correct row before they press it.



  • I agree with the above people, you should not be using Excel for this;

    @b_redeker said:

    @bombtek187 said:

    There will be 20-30 users of the sheet
     

    Bad. Only one user can edit the sheet at a time. You will have problems. Promise.

     

    Not exactly true; Excel tries to solve this (I use that term loosely) with the "Share Workbook" feature. It will force change tracking and a really basic form of conflict resolution. Check here for instructions in 2K7. It's available in 2K3 but I've never had the misfortune of encountering it before 2K7.



  • @rad131304 said:

    Not exactly true; Excel tries to solve this (I use that term loosely) with the "Share Workbook" feature. It will force change tracking and a really basic form of conflict resolution. Check here for instructions in 2K7. It's available in 2K3 but I've never had the misfortune of encountering it before 2K7.


    It worked about the same in 2K3 and, IIRC, in 2K. Which is to say, it was a festering pile of crap back then, too. It is possible to use it without everyone clobbering your data, but only if everyone follows the same procedure. Also, there are a ton of things that you can't do in a shared workbook, so you'll have to get used to the occasional cry of "Can everyone please get out of the spreadsheet? I need to make some changes." (Not being able to delete worksheets is sort of understandable, I suppose. But why can't you edit data validation rules?)



  • @Scarlet Manuka said:

    @rad131304 said:
    Not exactly true; Excel tries to solve this (I use that term loosely) with the "Share Workbook" feature. It will force change tracking and a really basic form of conflict resolution. Check here for instructions in 2K7. It's available in 2K3 but I've never had the misfortune of encountering it before 2K7.

    It worked about the same in 2K3 and, IIRC, in 2K. Which is to say, it was a festering pile of crap back then, too. It is possible to use it without everyone clobbering your data, but only if everyone follows the same procedure. Also, there are a ton of things that you can't do in a shared workbook, so you'll have to get used to the occasional cry of "Can everyone please get out of the spreadsheet? I need to make some changes." (Not being able to delete worksheets is sort of understandable, I suppose. But why can't you edit data validation rules?)

    I think TRWTF is you tried to do either of those on a shared workbook. The record structure should be immutable while it is shared. If you need to make a change to it, you unshare it and lock everyone out, make the change and release for use. You wouldn't just go modifying a production database while people are logged into it running queries would you?



  • @rad131304 said:

    @Scarlet Manuka said:

    (Not being able to delete worksheets is sort of understandable, I suppose. But why can't you edit data validation rules?)

    I think TRWTF is you tried to do either of those on a shared workbook. The record structure should be immutable while it is shared. If you need to make a change to it, you unshare it and lock everyone out, make the change and release for use. You wouldn't just go modifying a production database while people are logged into it running queries would you?

    If you think that I am going to take the production DB down and kick all the users out every time I add a new column to a table, you are TRWTF.

    In any case, does that also explain why you can't insert pictures, charts, hyperlinks or symbols, or edit the header and footer? (Also, typing in a URL won't make it into a hyperlink if the workbook is shared. Nor can you edit or remove existing hyperlinks.) The reason I picked the data validation one is because that's one that's actually caused me trouble in the past, whereas the number of times I've wanted to add pictures or hyperlinks to any Excel document, shared or not, is very small.

    And I find it risible that you postulate Excel's developers are so concerned with data integrity that they forbid all manner of relatively harmless actions, while offering no protection against any other user overwriting the data you enter, other than asking them whether they would rather sacrifice your data or their own. It's far more likely that they just disabled everything that they didn't have working merge code for.



  •  So this post has derailed nicely... Let me try this once more.  Since everyone that has replied to this post seems to be a brilliant programmer this shouldn't be too hard to get an answer for.  TRWTF is the fact that I posted a very specific question and needed a very specific answer and this forum has yet to provide that.  I will make a list so it will simplify any confusions.

     

    1.  I AM NOT A PROGRAMMER.

    2.  I am a worforce manager for an insurance company.

    3.  I cannot use any tools other than Excel to make this so please stop wasting your breath telling me how epic fail its going to be if I use Excel.  If your post is to tell me it will work better in Word then I am open to that idea (sarcasm).

    4.   I need the macro to know what cell the command button is in.

     

    p.s.  Please do not use my thread as an opportunity to regurgitate your vast knowledge of topics not related to my question.

     p.p.s(sp?) I apologize if this is rude, but I just need an answer to this.  



  • Did you not see my previous post? I've highlighted the part that most directly addresses your question, but please read the rest as well - you are almost certainly doing this The Wrong Way™.

    @Scarlet Manuka said:

    If you're going to have a button on every row, won't you have to have a [button name]_Click() event handler for each one? You can use that to call your macro with a row parameter (it should be easy enough to generate the code for that, assuming your button names are in a logical order).

    Failing that, [b]you can access the buttons' properties through the worksheet's Shapes collection. The TopLeftCell and BottomRightCell properties seem to be about what you want.[/b]

    But really, I'd just have a single button and instruct them to make sure the active cell is in the correct row before they press it.



  • The only way for you to get this to work is to expand a tiny bit into programming.

    Record your macro -- this will create a code module for the macro.

    Ok, put your buttons in place, they should end up named CommandButton1, CommandButton2, etc.

    Double Click each button which should take you to the VBA editor and into the Click code block for that button.

    Write a code block similar to the below for each button.  You will need to edit your macro to accept a parameter indicating which button was clicked, and edit the button clicks to run the macro and send it an identifier for the button:

    Private Sub CommandButton1_Click()
    WhateverTheMacroIs (1)
    End Sub

    Private Sub CommandButton2_Click()
    WhateverTheMacroIs (2)
    End Sub 

     

    Sub WhateverTheMacroIs(ButtonID As Long)
    Select Case ButtonID

    Case 1
     run the macro the way you should for Button 1
    Case 2
    run the macro the way you should for Button 2
    Case 3
    Etc.

    End Select
    End Sub

    The first Private Sub will be duplicated for each button instance, and the Parameter passed (in this case 1) will tell the real macro which button has been clicked.

    The Second subroutine will be the actual macro you want to run, and the case statement will specify which row/button was clicked.  Thus you only need to do a tiny bit of modification and a lot of copy and paste to create multiple buttons that run the macro multiple ways.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.