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.

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.

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.

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.

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.

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.

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.

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.