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 upBut 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:
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.@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?
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 SubPrivate Sub CommandButton2_Click()
WhateverTheMacroIs (2)
End SubSub WhateverTheMacroIs(ButtonID As Long)
Select Case ButtonIDCase 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 SubThe 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.
