Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Data into userform
I have a named Range starting at row 14, when I load the userform I
need 2 rows after the start of the named range to load. I named the range becuase the actual row will change from time to time. I am pretty new to VBA, can someone please assist. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Data into userform
I'm not sure how you want to load them or where there need to go but here is
a way to get the data: Dim r As Range Set r = Range("YourNamedRange").Cells(1).Resize(2).EntireR ow 'This sets "r" = to the first cell in your named range, plus the row below ' and includes the entire row. I'm not sure what else you want to do with it MsgBox r.Address -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: I have a named Range starting at row 14, when I load the userform I need 2 rows after the start of the named range to load. I named the range becuase the actual row will change from time to time. I am pretty new to VBA, can someone please assist. Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Data into userform
Thank you!!
Charles Chickering wrote: I'm not sure how you want to load them or where there need to go but here is a way to get the data: Dim r As Range Set r = Range("YourNamedRange").Cells(1).Resize(2).EntireR ow 'This sets "r" = to the first cell in your named range, plus the row below ' and includes the entire row. I'm not sure what else you want to do with it MsgBox r.Address -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: I have a named Range starting at row 14, when I load the userform I need 2 rows after the start of the named range to load. I named the range becuase the actual row will change from time to time. I am pretty new to VBA, can someone please assist. Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Data into userform
How would I set the CurrentRow in this case to load the data from the
first cell in the range? Charles Chickering wrote: I'm not sure how you want to load them or where there need to go but here is a way to get the data: Dim r As Range Set r = Range("YourNamedRange").Cells(1).Resize(2).EntireR ow 'This sets "r" = to the first cell in your named range, plus the row below ' and includes the entire row. I'm not sure what else you want to do with it MsgBox r.Address -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: I have a named Range starting at row 14, when I load the userform I need 2 rows after the start of the named range to load. I named the range becuase the actual row will change from time to time. I am pretty new to VBA, can someone please assist. Thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Data into userform
What do you mean by "CurrentRow" what are you loading the Spreadsheet data
into? What data do you want to look at? -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: How would I set the CurrentRow in this case to load the data from the first cell in the range? Charles Chickering wrote: I'm not sure how you want to load them or where there need to go but here is a way to get the data: Dim r As Range Set r = Range("YourNamedRange").Cells(1).Resize(2).EntireR ow 'This sets "r" = to the first cell in your named range, plus the row below ' and includes the entire row. I'm not sure what else you want to do with it MsgBox r.Address -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: I have a named Range starting at row 14, when I load the userform I need 2 rows after the start of the named range to load. I named the range becuase the actual row will change from time to time. I am pretty new to VBA, can someone please assist. Thank you. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Data into userform
My userform code is below. When the user activates the userform I need
the first row in the range we set earlier to load. As is, the data on the first row is loaded. Thanks for your help on this. Dim lCurrentRow As Long Private Sub cmdPrevious_Click() ' Show previous only if not already in first row: If lCurrentRow 1 Then ' Save form contents before changing rows: SaveRow ' Decrement row number: lCurrentRow = lCurrentRow - 1 ' Show contents of row in the form: LoadRow End If End Sub Private Sub cmdNext_Click() ' Save form contents before changing rows: SaveRow ' Increment row number: lCurrentRow = lCurrentRow + 1 ' Show contents of row in the form: LoadRow End Sub Private Sub cmdDelete_Click() Dim smessage As String smessage = "Are you sure you want to delete " + txtReqNum.Text + "?" If MsgBox(smessage, vbQuestion + vbYesNo, _ "Confirm Delete") = vbYes Then ' Delete current row Rows(lCurrentRow).Delete ' Show contents of new current row in the form: LoadRow End If End Sub Private Sub cmdAdd_Click() ' Save form contents before changing rows: SaveRow ' Set current row to first empty row, i.e. one row after ' the last row currently in use: If Cells(1, 1).Value = "" Then lCurrentRow = 1 ' (list is empty - start in row 1) Else lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1 End If ' Clear the form for user to add new name: LoadRow ' Set focus to Name textbox: txtReqNum.SetFocus End Sub Private Sub UserForm_Activate() ' Read initial values from Row 1: lCurrentRow = 1 LoadRow End Sub Private Sub cmdClose_Click() ' Save form contents before closing: SaveRow Unload Me ' Close the form End Sub Private Sub LoadRow() txtReqNum.Text = Cells(lCurrentRow, 1).Value txtDateOpen.Text = Cells(lCurrentRow, 2).Value txtType.Text = Cells(lCurrentRow, 4).Value txtPriority.Text = Cells(lCurrentRow, 5).Value txtTitle.Text = Cells(lCurrentRow, 6).Value txtGrd.Text = Cells(lCurrentRow, 7).Value txtRange.Text = Cells(lCurrentRow, 8).Value txtExpected.Text = Cells(lCurrentRow, 9).Value txtNR.Text = Cells(lCurrentRow, 11).Value txtManager.Text = Cells(lCurrentRow, 12).Value txtRecr.Text = Cells(lCurrentRow, 13).Value txtStatus.Text = Cells(lCurrentRow, 14).Value txtCandidate.Text = Cells(lCurrentRow, 15).Value End Sub Private Sub SaveRow() Cells(lCurrentRow, 1).Value = txtReqNum.Text Cells(lCurrentRow, 2).Value = txtDateOpen.Text Cells(lCurrentRow, 4).Value = txtType.Text Cells(lCurrentRow, 5).Value = txtPriority.Text Cells(lCurrentRow, 6).Value = txtTitle.Text Cells(lCurrentRow, 7).Value = txtGrd.Text Cells(lCurrentRow, 8).Value = txtRange.Text Cells(lCurrentRow, 9).Value = txtExpected.Text Cells(lCurrentRow, 11).Value = txtNR.Text Cells(lCurrentRow, 12).Value = txtManager.Text Cells(lCurrentRow, 13).Value = txtRecr.Text Cells(lCurrentRow, 14).Value = txtStatus.Text Cells(lCurrentRow, 15).Value = txtCandidate.Text End Sub Charles Chickering wrote: What do you mean by "CurrentRow" what are you loading the Spreadsheet data into? What data do you want to look at? -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: How would I set the CurrentRow in this case to load the data from the first cell in the range? Charles Chickering wrote: I'm not sure how you want to load them or where there need to go but here is a way to get the data: Dim r As Range Set r = Range("YourNamedRange").Cells(1).Resize(2).EntireR ow 'This sets "r" = to the first cell in your named range, plus the row below ' and includes the entire row. I'm not sure what else you want to do with it MsgBox r.Address -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: I have a named Range starting at row 14, when I load the userform I need 2 rows after the start of the named range to load. I named the range becuase the actual row will change from time to time. I am pretty new to VBA, can someone please assist. Thank you. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Data into userform
Oh... Now I see what you are doing... Try this:
Private Sub UserForm_Activate() ' Read initial values from Row 1: lCurrentRow = Range("MyNamedRange").Cells(1).Row 'First Row lCurrentRow = Range("MyNamedRange").Cells(2,1).Row 'Second Row LoadRow End Sub Does that make sense? Basically when you have a multiple cell range, you can use the .Cells property to single out a certain cell, then you can use the ..Row property of the .Cells property to find the absolute Row in the spreadsheet. -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: My userform code is below. When the user activates the userform I need the first row in the range we set earlier to load. As is, the data on the first row is loaded. Thanks for your help on this. Dim lCurrentRow As Long Private Sub cmdPrevious_Click() ' Show previous only if not already in first row: If lCurrentRow 1 Then ' Save form contents before changing rows: SaveRow ' Decrement row number: lCurrentRow = lCurrentRow - 1 ' Show contents of row in the form: LoadRow End If End Sub Private Sub cmdNext_Click() ' Save form contents before changing rows: SaveRow ' Increment row number: lCurrentRow = lCurrentRow + 1 ' Show contents of row in the form: LoadRow End Sub Private Sub cmdDelete_Click() Dim smessage As String smessage = "Are you sure you want to delete " + txtReqNum.Text + "?" If MsgBox(smessage, vbQuestion + vbYesNo, _ "Confirm Delete") = vbYes Then ' Delete current row Rows(lCurrentRow).Delete ' Show contents of new current row in the form: LoadRow End If End Sub Private Sub cmdAdd_Click() ' Save form contents before changing rows: SaveRow ' Set current row to first empty row, i.e. one row after ' the last row currently in use: If Cells(1, 1).Value = "" Then lCurrentRow = 1 ' (list is empty - start in row 1) Else lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1 End If ' Clear the form for user to add new name: LoadRow ' Set focus to Name textbox: txtReqNum.SetFocus End Sub Private Sub UserForm_Activate() ' Read initial values from Row 1: lCurrentRow = 1 LoadRow End Sub Private Sub cmdClose_Click() ' Save form contents before closing: SaveRow Unload Me ' Close the form End Sub Private Sub LoadRow() txtReqNum.Text = Cells(lCurrentRow, 1).Value txtDateOpen.Text = Cells(lCurrentRow, 2).Value txtType.Text = Cells(lCurrentRow, 4).Value txtPriority.Text = Cells(lCurrentRow, 5).Value txtTitle.Text = Cells(lCurrentRow, 6).Value txtGrd.Text = Cells(lCurrentRow, 7).Value txtRange.Text = Cells(lCurrentRow, 8).Value txtExpected.Text = Cells(lCurrentRow, 9).Value txtNR.Text = Cells(lCurrentRow, 11).Value txtManager.Text = Cells(lCurrentRow, 12).Value txtRecr.Text = Cells(lCurrentRow, 13).Value txtStatus.Text = Cells(lCurrentRow, 14).Value txtCandidate.Text = Cells(lCurrentRow, 15).Value End Sub Private Sub SaveRow() Cells(lCurrentRow, 1).Value = txtReqNum.Text Cells(lCurrentRow, 2).Value = txtDateOpen.Text Cells(lCurrentRow, 4).Value = txtType.Text Cells(lCurrentRow, 5).Value = txtPriority.Text Cells(lCurrentRow, 6).Value = txtTitle.Text Cells(lCurrentRow, 7).Value = txtGrd.Text Cells(lCurrentRow, 8).Value = txtRange.Text Cells(lCurrentRow, 9).Value = txtExpected.Text Cells(lCurrentRow, 11).Value = txtNR.Text Cells(lCurrentRow, 12).Value = txtManager.Text Cells(lCurrentRow, 13).Value = txtRecr.Text Cells(lCurrentRow, 14).Value = txtStatus.Text Cells(lCurrentRow, 15).Value = txtCandidate.Text End Sub Charles Chickering wrote: What do you mean by "CurrentRow" what are you loading the Spreadsheet data into? What data do you want to look at? -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: How would I set the CurrentRow in this case to load the data from the first cell in the range? Charles Chickering wrote: I'm not sure how you want to load them or where there need to go but here is a way to get the data: Dim r As Range Set r = Range("YourNamedRange").Cells(1).Resize(2).EntireR ow 'This sets "r" = to the first cell in your named range, plus the row below ' and includes the entire row. I'm not sure what else you want to do with it MsgBox r.Address -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: I have a named Range starting at row 14, when I load the userform I need 2 rows after the start of the named range to load. I named the range becuase the actual row will change from time to time. I am pretty new to VBA, can someone please assist. Thank you. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Data into userform
Yes, it does make sense and this worked. Thank you very much!
Charles Chickering wrote: Oh... Now I see what you are doing... Try this: Private Sub UserForm_Activate() ' Read initial values from Row 1: lCurrentRow = Range("MyNamedRange").Cells(1).Row 'First Row lCurrentRow = Range("MyNamedRange").Cells(2,1).Row 'Second Row LoadRow End Sub Does that make sense? Basically when you have a multiple cell range, you can use the .Cells property to single out a certain cell, then you can use the .Row property of the .Cells property to find the absolute Row in the spreadsheet. -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: My userform code is below. When the user activates the userform I need the first row in the range we set earlier to load. As is, the data on the first row is loaded. Thanks for your help on this. Dim lCurrentRow As Long Private Sub cmdPrevious_Click() ' Show previous only if not already in first row: If lCurrentRow 1 Then ' Save form contents before changing rows: SaveRow ' Decrement row number: lCurrentRow = lCurrentRow - 1 ' Show contents of row in the form: LoadRow End If End Sub Private Sub cmdNext_Click() ' Save form contents before changing rows: SaveRow ' Increment row number: lCurrentRow = lCurrentRow + 1 ' Show contents of row in the form: LoadRow End Sub Private Sub cmdDelete_Click() Dim smessage As String smessage = "Are you sure you want to delete " + txtReqNum.Text + "?" If MsgBox(smessage, vbQuestion + vbYesNo, _ "Confirm Delete") = vbYes Then ' Delete current row Rows(lCurrentRow).Delete ' Show contents of new current row in the form: LoadRow End If End Sub Private Sub cmdAdd_Click() ' Save form contents before changing rows: SaveRow ' Set current row to first empty row, i.e. one row after ' the last row currently in use: If Cells(1, 1).Value = "" Then lCurrentRow = 1 ' (list is empty - start in row 1) Else lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1 End If ' Clear the form for user to add new name: LoadRow ' Set focus to Name textbox: txtReqNum.SetFocus End Sub Private Sub UserForm_Activate() ' Read initial values from Row 1: lCurrentRow = 1 LoadRow End Sub Private Sub cmdClose_Click() ' Save form contents before closing: SaveRow Unload Me ' Close the form End Sub Private Sub LoadRow() txtReqNum.Text = Cells(lCurrentRow, 1).Value txtDateOpen.Text = Cells(lCurrentRow, 2).Value txtType.Text = Cells(lCurrentRow, 4).Value txtPriority.Text = Cells(lCurrentRow, 5).Value txtTitle.Text = Cells(lCurrentRow, 6).Value txtGrd.Text = Cells(lCurrentRow, 7).Value txtRange.Text = Cells(lCurrentRow, 8).Value txtExpected.Text = Cells(lCurrentRow, 9).Value txtNR.Text = Cells(lCurrentRow, 11).Value txtManager.Text = Cells(lCurrentRow, 12).Value txtRecr.Text = Cells(lCurrentRow, 13).Value txtStatus.Text = Cells(lCurrentRow, 14).Value txtCandidate.Text = Cells(lCurrentRow, 15).Value End Sub Private Sub SaveRow() Cells(lCurrentRow, 1).Value = txtReqNum.Text Cells(lCurrentRow, 2).Value = txtDateOpen.Text Cells(lCurrentRow, 4).Value = txtType.Text Cells(lCurrentRow, 5).Value = txtPriority.Text Cells(lCurrentRow, 6).Value = txtTitle.Text Cells(lCurrentRow, 7).Value = txtGrd.Text Cells(lCurrentRow, 8).Value = txtRange.Text Cells(lCurrentRow, 9).Value = txtExpected.Text Cells(lCurrentRow, 11).Value = txtNR.Text Cells(lCurrentRow, 12).Value = txtManager.Text Cells(lCurrentRow, 13).Value = txtRecr.Text Cells(lCurrentRow, 14).Value = txtStatus.Text Cells(lCurrentRow, 15).Value = txtCandidate.Text End Sub Charles Chickering wrote: What do you mean by "CurrentRow" what are you loading the Spreadsheet data into? What data do you want to look at? -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: How would I set the CurrentRow in this case to load the data from the first cell in the range? Charles Chickering wrote: I'm not sure how you want to load them or where there need to go but here is a way to get the data: Dim r As Range Set r = Range("YourNamedRange").Cells(1).Resize(2).EntireR ow 'This sets "r" = to the first cell in your named range, plus the row below ' and includes the entire row. I'm not sure what else you want to do with it MsgBox r.Address -- Charles Chickering "A good example is twice the value of good advice." "Chris" wrote: I have a named Range starting at row 14, when I load the userform I need 2 rows after the start of the named range to load. I named the range becuase the actual row will change from time to time. I am pretty new to VBA, can someone please assist. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A save/load txt UserForm | Excel Programming | |||
LOAD userform | Excel Programming | |||
load/unload userform | Excel Programming | |||
How Do I Load A ComboBox On A UserForm | Excel Programming | |||
Load a Userform | Excel Programming |