| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
use active cell to determine range
I am using the following code to import record from another sheet so the
record can be modified. The user has to look at the "Records" sheet, find the row number of the record they want to modify, move to the AddRecord sheet and input the row number in cell "A41" and click on the ImportRecordBtn. I want to have the user click on a record on the "Records" sheet and then click on ImportRecordBtn on the same sheet and have the record move to the AddRecord sheet. Sub ImportRecordBtn() 'Import data on AddRecord Sheet based on data selected from Records Sheet ActiveWorkbook.Unprotect Password:="xxx" Sheets("Records").Unprotect Password:="xxx" Dim LName As String Dim LType As String Dim LAffectedSystem As String Dim LNote As String Dim LHours As Integer Dim LModule As String Dim LSubAssy As String Dim LComponent As String Dim LWaferCount As String Dim LToolType As String Dim LSerNo As Integer Dim LResolved As String Dim LKeyword As String Dim LDept As String Dim LOwner As String Dim LFTEs As Integer Dim LRecordNo As String Dim LRow As Long Dim LFound As Boolean LRow = Range(A41).Address ' Move to Record sheet and record values based on row selected. 'Sheets("Records").Activate 'Sheets("Records").Unprotect Password:="xxx" LName = Range("C" & LRow).Value LNote = Range("D" & LRow).Value LOwner = Range("E" & LRow).Value LDept = Range("F" & LRow).Value LType = Range("H" & LRow).Value Range("I" & LRow).Value = "Yes" Range("I" & LRow).Font.ColorIndex = 3 LToolType = Range("J" & LRow).Value LSerNo = Range("K" & LRow).Value LAffected = Range("L" & LRow).Value LModule = Range("M" & LRow).Value LSubAssy = Range("N" & LRow).Value LComponent = Range("O" & LRow).Value LHours = Range("P" & LRow).Value LWaferCount = Range("Q" & LRow).Value LKeyword = Range("R" & LRow).Value LFTEs = Range("T" & LRow).Value LRecordNo = Range("U" & LRow).Value Sheets("Records").Protect Password:="xxx" ' Move to AddRecords sheet and input data based on Records sheet and Row selected Sheets("AddRecords").Visible = True Sheets("Records").Visible = False Sheets("AddRecords").Activate Sheets("AddRecords").Unprotect Password:="xxx" Range("B4").Value = LName Range("C4").Value = LToolType Range("D4").Value = LSerNo Range("E4").Value = LWaferCount Range("A8").Value = LType Range("B8").Value = LAffected Range("C8").Value = LModule Range("D8").Value = LSubAssy Range("E8").Value = LComponent Range("A12").Value = LKeyword Range("B12").Value = LNote Range("E12").Value = LHours Range("F12").Value = LFTEs Range("G12").Value = "No" Range("H5").Value = LDept Range("I5").Value = LOwner Range("A19").Value = LRecordNo 'Clear entries from cells Range("E12").Value = "" Sheets("AddRecords").Protect Password:="xxx" ActiveWorkbook.Protect Password:="xxx" End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
use active cell to determine range
As long as the button is on the Records sheet, I think you could get away with
doing this: LRow = Range("A41").Value But maybe you could just use the row with the activecell??? LRow = activecell.row Gizmo wrote: I am using the following code to import record from another sheet so the record can be modified. The user has to look at the "Records" sheet, find the row number of the record they want to modify, move to the AddRecord sheet and input the row number in cell "A41" and click on the ImportRecordBtn. I want to have the user click on a record on the "Records" sheet and then click on ImportRecordBtn on the same sheet and have the record move to the AddRecord sheet. Sub ImportRecordBtn() 'Import data on AddRecord Sheet based on data selected from Records Sheet ActiveWorkbook.Unprotect Password:="xxx" Sheets("Records").Unprotect Password:="xxx" Dim LName As String Dim LType As String Dim LAffectedSystem As String Dim LNote As String Dim LHours As Integer Dim LModule As String Dim LSubAssy As String Dim LComponent As String Dim LWaferCount As String Dim LToolType As String Dim LSerNo As Integer Dim LResolved As String Dim LKeyword As String Dim LDept As String Dim LOwner As String Dim LFTEs As Integer Dim LRecordNo As String Dim LRow As Long Dim LFound As Boolean LRow = Range(A41).Address ' Move to Record sheet and record values based on row selected. 'Sheets("Records").Activate 'Sheets("Records").Unprotect Password:="xxx" LName = Range("C" & LRow).Value LNote = Range("D" & LRow).Value LOwner = Range("E" & LRow).Value LDept = Range("F" & LRow).Value LType = Range("H" & LRow).Value Range("I" & LRow).Value = "Yes" Range("I" & LRow).Font.ColorIndex = 3 LToolType = Range("J" & LRow).Value LSerNo = Range("K" & LRow).Value LAffected = Range("L" & LRow).Value LModule = Range("M" & LRow).Value LSubAssy = Range("N" & LRow).Value LComponent = Range("O" & LRow).Value LHours = Range("P" & LRow).Value LWaferCount = Range("Q" & LRow).Value LKeyword = Range("R" & LRow).Value LFTEs = Range("T" & LRow).Value LRecordNo = Range("U" & LRow).Value Sheets("Records").Protect Password:="xxx" ' Move to AddRecords sheet and input data based on Records sheet and Row selected Sheets("AddRecords").Visible = True Sheets("Records").Visible = False Sheets("AddRecords").Activate Sheets("AddRecords").Unprotect Password:="xxx" Range("B4").Value = LName Range("C4").Value = LToolType Range("D4").Value = LSerNo Range("E4").Value = LWaferCount Range("A8").Value = LType Range("B8").Value = LAffected Range("C8").Value = LModule Range("D8").Value = LSubAssy Range("E8").Value = LComponent Range("A12").Value = LKeyword Range("B12").Value = LNote Range("E12").Value = LHours Range("F12").Value = LFTEs Range("G12").Value = "No" Range("H5").Value = LDept Range("I5").Value = LOwner Range("A19").Value = LRecordNo 'Clear entries from cells Range("E12").Value = "" Sheets("AddRecords").Protect Password:="xxx" ActiveWorkbook.Protect Password:="xxx" End Sub -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
use active cell to determine range
Dave,
Thank you very much. LRow = activecell.row did the trick. I've been trying all sorts of combinations including LRow=Range("A41").Value and nothing worked. Thanks again. "Dave Peterson" wrote: As long as the button is on the Records sheet, I think you could get away with doing this: LRow = Range("A41").Value But maybe you could just use the row with the activecell??? LRow = activecell.row Gizmo wrote: I am using the following code to import record from another sheet so the record can be modified. The user has to look at the "Records" sheet, find the row number of the record they want to modify, move to the AddRecord sheet and input the row number in cell "A41" and click on the ImportRecordBtn. I want to have the user click on a record on the "Records" sheet and then click on ImportRecordBtn on the same sheet and have the record move to the AddRecord sheet. Sub ImportRecordBtn() 'Import data on AddRecord Sheet based on data selected from Records Sheet ActiveWorkbook.Unprotect Password:="xxx" Sheets("Records").Unprotect Password:="xxx" Dim LName As String Dim LType As String Dim LAffectedSystem As String Dim LNote As String Dim LHours As Integer Dim LModule As String Dim LSubAssy As String Dim LComponent As String Dim LWaferCount As String Dim LToolType As String Dim LSerNo As Integer Dim LResolved As String Dim LKeyword As String Dim LDept As String Dim LOwner As String Dim LFTEs As Integer Dim LRecordNo As String Dim LRow As Long Dim LFound As Boolean LRow = Range(A41).Address ' Move to Record sheet and record values based on row selected. 'Sheets("Records").Activate 'Sheets("Records").Unprotect Password:="xxx" LName = Range("C" & LRow).Value LNote = Range("D" & LRow).Value LOwner = Range("E" & LRow).Value LDept = Range("F" & LRow).Value LType = Range("H" & LRow).Value Range("I" & LRow).Value = "Yes" Range("I" & LRow).Font.ColorIndex = 3 LToolType = Range("J" & LRow).Value LSerNo = Range("K" & LRow).Value LAffected = Range("L" & LRow).Value LModule = Range("M" & LRow).Value LSubAssy = Range("N" & LRow).Value LComponent = Range("O" & LRow).Value LHours = Range("P" & LRow).Value LWaferCount = Range("Q" & LRow).Value LKeyword = Range("R" & LRow).Value LFTEs = Range("T" & LRow).Value LRecordNo = Range("U" & LRow).Value Sheets("Records").Protect Password:="xxx" ' Move to AddRecords sheet and input data based on Records sheet and Row selected Sheets("AddRecords").Visible = True Sheets("Records").Visible = False Sheets("AddRecords").Activate Sheets("AddRecords").Unprotect Password:="xxx" Range("B4").Value = LName Range("C4").Value = LToolType Range("D4").Value = LSerNo Range("E4").Value = LWaferCount Range("A8").Value = LType Range("B8").Value = LAffected Range("C8").Value = LModule Range("D8").Value = LSubAssy Range("E8").Value = LComponent Range("A12").Value = LKeyword Range("B12").Value = LNote Range("E12").Value = LHours Range("F12").Value = LFTEs Range("G12").Value = "No" Range("H5").Value = LDept Range("I5").Value = LOwner Range("A19").Value = LRecordNo 'Clear entries from cells Range("E12").Value = "" Sheets("AddRecords").Protect Password:="xxx" ActiveWorkbook.Protect Password:="xxx" End Sub -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Clearly seeing active cell in a range | Excel Discussion (Misc queries) | |||
| How to Determine if Application Window Still Active | Excel Discussion (Misc queries) | |||
| Value of cell to determine range in MAX Function | Excel Worksheet Functions | |||
| Determine if Cell Address is within a Range | Excel Worksheet Functions | |||
| Function to determine if any cell in a range is contained in a given cell | Excel Worksheet Functions | |||
Linear Mode
