Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get info from first cell in row.
I have created a macro to generate an automated email notification when a
command button is clicked, which uses data from the active cell and cells near it using ActiveCell(1,x) where x is the number of cells to the right of the active cell i want information from. The problem is, It only works properly if the first cell in the row is the Active Cell. I am trying to make it a little more idiot proof. (Less computer literate personnel may be using this spreadsheet) Is there a way to either move the active cell to the beginning of the row when the macro starts, or referance the cells directly in the current row? (ie, get info from cells in column A, B, and E of current row) Here is the code I have now: Sub InitialNotification() ' Is working in Office 2000-2007 ' Generates an initial notification email Dim OutApp As Object Dim OutMail As Object Dim strbody As String Dim FlightDate As String Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) strbody = _ Chr(13) & _ Chr(13) & _ ActiveCell(1, 2) & ", " & Chr(13) & _ Chr(13) & _ "An EPR must be written for " & ActiveCell & ". " & Chr(13) & _ "Suspense dates are listed below. " & Chr(13) & _ Chr(13) & _ "Flight: " & ActiveCell(1, 3) & Chr(13) & _ "Squadron: " & ActiveCell(1, 4) & Chr(13) & _ "Closeout: " & ActiveCell(1, 5) & Chr(13) & _ Chr(13) & _ "Link: <file://S:\CCS\TSgt & Below EPRs\MXMW" & Chr(13) & _ Chr(13) & _ Chr(13) SigString = "C:\Documents and Settings\" & Environ("username") & _ "\Application Data\Microsoft\Signatures\FOUO.txt" 'Replace FOUO with the name of your 'signature to make this work If Dir(SigString) < "" Then Signature = GetBoiler(SigString) Else Signature = "" End If With OutMail .To = "" .CC = "" .BCC = "" .Subject = "EPR- " & ActiveCell .Body = strbody & Signature .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub This function is also included: Function GetBoiler(ByVal sFile As String) As String 'Dick Kusleika ' This is used by all three email reminder generating Subs Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get info from first cell in row.
You can reference the first cell of the row containing the ActiveCell
with code like: Dim R As Range Set R = ActiveCell.EntireRow.Cells(1,"A") 'reference other cells in the row with code like Debug.Print R.EntireRow.Cells(1,"B") ' column B Debug.Print R.EntireRow.Cells(1,"E") ' column E Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 16 Oct 2008 09:50:04 -0700, voayger2001dl wrote: I have created a macro to generate an automated email notification when a command button is clicked, which uses data from the active cell and cells near it using ActiveCell(1,x) where x is the number of cells to the right of the active cell i want information from. The problem is, It only works properly if the first cell in the row is the Active Cell. I am trying to make it a little more idiot proof. (Less computer literate personnel may be using this spreadsheet) Is there a way to either move the active cell to the beginning of the row when the macro starts, or referance the cells directly in the current row? (ie, get info from cells in column A, B, and E of current row) Here is the code I have now: Sub InitialNotification() ' Is working in Office 2000-2007 ' Generates an initial notification email Dim OutApp As Object Dim OutMail As Object Dim strbody As String Dim FlightDate As String Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) strbody = _ Chr(13) & _ Chr(13) & _ ActiveCell(1, 2) & ", " & Chr(13) & _ Chr(13) & _ "An EPR must be written for " & ActiveCell & ". " & Chr(13) & _ "Suspense dates are listed below. " & Chr(13) & _ Chr(13) & _ "Flight: " & ActiveCell(1, 3) & Chr(13) & _ "Squadron: " & ActiveCell(1, 4) & Chr(13) & _ "Closeout: " & ActiveCell(1, 5) & Chr(13) & _ Chr(13) & _ "Link: <file://S:\CCS\TSgt & Below EPRs\MXMW" & Chr(13) & _ Chr(13) & _ Chr(13) SigString = "C:\Documents and Settings\" & Environ("username") & _ "\Application Data\Microsoft\Signatures\FOUO.txt" 'Replace FOUO with the name of your 'signature to make this work If Dir(SigString) < "" Then Signature = GetBoiler(SigString) Else Signature = "" End If With OutMail .To = "" .CC = "" .BCC = "" .Subject = "EPR- " & ActiveCell .Body = strbody & Signature .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub This function is also included: Function GetBoiler(ByVal sFile As String) As String 'Dick Kusleika ' This is used by all three email reminder generating Subs Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get info from first cell in row.
If your references are in row 1, you can make the first line of the macro:
ActiveSheet.Range("A1").Activate If you want to refer to a specific column in the row with the active cell then: Range("C" & ActiveCell.Row).Activate Range("D" & ActiveCell.Row).Activate Or myVar = Range("E" & ActiveCell.Row).Value You can get additional information by looking up these two topics in the VBA help files. Press Alt + F11 to access the VBA help files from the menu bar. "How to reference cells and ranges" "Referring to cells relative to other Cells" "voayger2001dl" wrote: I have created a macro to generate an automated email notification when a command button is clicked, which uses data from the active cell and cells near it using ActiveCell(1,x) where x is the number of cells to the right of the active cell i want information from. The problem is, It only works properly if the first cell in the row is the Active Cell. I am trying to make it a little more idiot proof. (Less computer literate personnel may be using this spreadsheet) Is there a way to either move the active cell to the beginning of the row when the macro starts, or referance the cells directly in the current row? (ie, get info from cells in column A, B, and E of current row) Here is the code I have now: Sub InitialNotification() ' Is working in Office 2000-2007 ' Generates an initial notification email Dim OutApp As Object Dim OutMail As Object Dim strbody As String Dim FlightDate As String Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) strbody = _ Chr(13) & _ Chr(13) & _ ActiveCell(1, 2) & ", " & Chr(13) & _ Chr(13) & _ "An EPR must be written for " & ActiveCell & ". " & Chr(13) & _ "Suspense dates are listed below. " & Chr(13) & _ Chr(13) & _ "Flight: " & ActiveCell(1, 3) & Chr(13) & _ "Squadron: " & ActiveCell(1, 4) & Chr(13) & _ "Closeout: " & ActiveCell(1, 5) & Chr(13) & _ Chr(13) & _ "Link: <file://S:\CCS\TSgt & Below EPRs\MXMW" & Chr(13) & _ Chr(13) & _ Chr(13) SigString = "C:\Documents and Settings\" & Environ("username") & _ "\Application Data\Microsoft\Signatures\FOUO.txt" 'Replace FOUO with the name of your 'signature to make this work If Dir(SigString) < "" Then Signature = GetBoiler(SigString) Else Signature = "" End If With OutMail .To = "" .CC = "" .BCC = "" .Subject = "EPR- " & ActiveCell .Body = strbody & Signature .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub This function is also included: Function GetBoiler(ByVal sFile As String) As String 'Dick Kusleika ' This is used by all three email reminder generating Subs Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get info from first cell in row.
Yep, this works great.
Reply was quick, thanks. I had tried looking in VBA help and was unable to find what I needed because I didn't know the syntax. Thanks alot. "JLGWhiz" wrote: If your references are in row 1, you can make the first line of the macro: ActiveSheet.Range("A1").Activate If you want to refer to a specific column in the row with the active cell then: Range("C" & ActiveCell.Row).Activate Range("D" & ActiveCell.Row).Activate Or myVar = Range("E" & ActiveCell.Row).Value You can get additional information by looking up these two topics in the VBA help files. Press Alt + F11 to access the VBA help files from the menu bar. "How to reference cells and ranges" "Referring to cells relative to other Cells" "voayger2001dl" wrote: I have created a macro to generate an automated email notification when a command button is clicked, which uses data from the active cell and cells near it using ActiveCell(1,x) where x is the number of cells to the right of the active cell i want information from. The problem is, It only works properly if the first cell in the row is the Active Cell. I am trying to make it a little more idiot proof. (Less computer literate personnel may be using this spreadsheet) Is there a way to either move the active cell to the beginning of the row when the macro starts, or referance the cells directly in the current row? (ie, get info from cells in column A, B, and E of current row) Here is the code I have now: Sub InitialNotification() ' Is working in Office 2000-2007 ' Generates an initial notification email Dim OutApp As Object Dim OutMail As Object Dim strbody As String Dim FlightDate As String Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) strbody = _ Chr(13) & _ Chr(13) & _ ActiveCell(1, 2) & ", " & Chr(13) & _ Chr(13) & _ "An EPR must be written for " & ActiveCell & ". " & Chr(13) & _ "Suspense dates are listed below. " & Chr(13) & _ Chr(13) & _ "Flight: " & ActiveCell(1, 3) & Chr(13) & _ "Squadron: " & ActiveCell(1, 4) & Chr(13) & _ "Closeout: " & ActiveCell(1, 5) & Chr(13) & _ Chr(13) & _ "Link: <file://S:\CCS\TSgt & Below EPRs\MXMW" & Chr(13) & _ Chr(13) & _ Chr(13) SigString = "C:\Documents and Settings\" & Environ("username") & _ "\Application Data\Microsoft\Signatures\FOUO.txt" 'Replace FOUO with the name of your 'signature to make this work If Dir(SigString) < "" Then Signature = GetBoiler(SigString) Else Signature = "" End If With OutMail .To = "" .CC = "" .BCC = "" .Subject = "EPR- " & ActiveCell .Body = strbody & Signature .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub This function is also included: Function GetBoiler(ByVal sFile As String) As String 'Dick Kusleika ' This is used by all three email reminder generating Subs Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get info from first cell in row.
Taking a closer look at your code, I don't know why the absolute cell
references could not be used. Unless I missed something, there is nothing that makes the use of relative refences necessary, like changing rows. It appears that all the information is on one row and that the data in each cell is constant and applied to the same places within the email for each application. So you could just as easily use: Range("A2").Value Instead of ActiveCell(1, 2), assuming A1 is the active cell. And the other cells could all be treated the same. Range("A3").Value for ActiveCell(1,3), etc. "voayger2001dl" wrote: I have created a macro to generate an automated email notification when a command button is clicked, which uses data from the active cell and cells near it using ActiveCell(1,x) where x is the number of cells to the right of the active cell i want information from. The problem is, It only works properly if the first cell in the row is the Active Cell. I am trying to make it a little more idiot proof. (Less computer literate personnel may be using this spreadsheet) Is there a way to either move the active cell to the beginning of the row when the macro starts, or referance the cells directly in the current row? (ie, get info from cells in column A, B, and E of current row) Here is the code I have now: Sub InitialNotification() ' Is working in Office 2000-2007 ' Generates an initial notification email Dim OutApp As Object Dim OutMail As Object Dim strbody As String Dim FlightDate As String Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) strbody = _ Chr(13) & _ Chr(13) & _ ActiveCell(1, 2) & ", " & Chr(13) & _ Chr(13) & _ "An EPR must be written for " & ActiveCell & ". " & Chr(13) & _ "Suspense dates are listed below. " & Chr(13) & _ Chr(13) & _ "Flight: " & ActiveCell(1, 3) & Chr(13) & _ "Squadron: " & ActiveCell(1, 4) & Chr(13) & _ "Closeout: " & ActiveCell(1, 5) & Chr(13) & _ Chr(13) & _ "Link: <file://S:\CCS\TSgt & Below EPRs\MXMW" & Chr(13) & _ Chr(13) & _ Chr(13) SigString = "C:\Documents and Settings\" & Environ("username") & _ "\Application Data\Microsoft\Signatures\FOUO.txt" 'Replace FOUO with the name of your 'signature to make this work If Dir(SigString) < "" Then Signature = GetBoiler(SigString) Else Signature = "" End If With OutMail .To = "" .CC = "" .BCC = "" .Subject = "EPR- " & ActiveCell .Body = strbody & Signature .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub This function is also included: Function GetBoiler(ByVal sFile As String) As String 'Dick Kusleika ' This is used by all three email reminder generating Subs Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get info from first cell in row.
I change rows manually. I have to enter data into the spreadsheet which
comes to me in emails. And depending on circumstances the notification may need to go to different people. So automation of that degree is just not practical. This way, I simply select any cell in the line concerning the report I want to notify/remind someone of, and start the macro(via command button I added) to get an email filled out with the proper names and dates from that row. Each row shows status on a report from a different person. The macro just keeps me from having to type the same emails 50 times a day. Thanks for your help though. I used your second option, setting each relative referance as a variable. It works great, and my code is much easier to understand now as well. "JLGWhiz" wrote: Taking a closer look at your code, I don't know why the absolute cell references could not be used. Unless I missed something, there is nothing that makes the use of relative refences necessary, like changing rows. It appears that all the information is on one row and that the data in each cell is constant and applied to the same places within the email for each application. So you could just as easily use: Range("A2").Value Instead of ActiveCell(1, 2), assuming A1 is the active cell. And the other cells could all be treated the same. Range("A3").Value for ActiveCell(1,3), etc. "voayger2001dl" wrote: I have created a macro to generate an automated email notification when a command button is clicked, which uses data from the active cell and cells near it using ActiveCell(1,x) where x is the number of cells to the right of the active cell i want information from. The problem is, It only works properly if the first cell in the row is the Active Cell. I am trying to make it a little more idiot proof. (Less computer literate personnel may be using this spreadsheet) Is there a way to either move the active cell to the beginning of the row when the macro starts, or referance the cells directly in the current row? (ie, get info from cells in column A, B, and E of current row) Here is the code I have now: Sub InitialNotification() ' Is working in Office 2000-2007 ' Generates an initial notification email Dim OutApp As Object Dim OutMail As Object Dim strbody As String Dim FlightDate As String Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) strbody = _ Chr(13) & _ Chr(13) & _ ActiveCell(1, 2) & ", " & Chr(13) & _ Chr(13) & _ "An EPR must be written for " & ActiveCell & ". " & Chr(13) & _ "Suspense dates are listed below. " & Chr(13) & _ Chr(13) & _ "Flight: " & ActiveCell(1, 3) & Chr(13) & _ "Squadron: " & ActiveCell(1, 4) & Chr(13) & _ "Closeout: " & ActiveCell(1, 5) & Chr(13) & _ Chr(13) & _ "Link: <file://S:\CCS\TSgt & Below EPRs\MXMW" & Chr(13) & _ Chr(13) & _ Chr(13) SigString = "C:\Documents and Settings\" & Environ("username") & _ "\Application Data\Microsoft\Signatures\FOUO.txt" 'Replace FOUO with the name of your 'signature to make this work If Dir(SigString) < "" Then Signature = GetBoiler(SigString) Else Signature = "" End If With OutMail .To = "" .CC = "" .BCC = "" .Subject = "EPR- " & ActiveCell .Body = strbody & Signature .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub This function is also included: Function GetBoiler(ByVal sFile As String) As String 'Dick Kusleika ' This is used by all three email reminder generating Subs Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2) GetBoiler = ts.readall ts.Close End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move cell info and info in range of cells on new entry | Excel Discussion (Misc queries) | |||
Copy info into empty cells below info, until finds cell with new d | Excel Discussion (Misc queries) | |||
Move cell info and info in neighboring cell on new entry | Excel Discussion (Misc queries) | |||
Link info in one cell to info in several cells in another column (like a database) | Excel Discussion (Misc queries) | |||
how do i get excel to see info in one cell, look at info in anoth. | Excel Discussion (Misc queries) |