User Form to Modify Row
 

I have created a user form that collects information from a worksheet
and puts it into text boxes. I did this with a list box that has a
named range as a source.

Now that I have the information in the text boxes I will only allow a
couple of them to be modified. Once they are modified I need to
overwrite them to the row based on the value of one of the cells. It is
actually a quote number that is in the first row.

One more thing, the worksheet is not active.

How do I set a row active by a value of a text box?

Thank You,


--
BBoner
------------------------------------------------------------------------
BBoner's Profile: http://www.excelforum.com/member.php...fo&userid=5908
View this thread: http://www.excelforum.com/showthread...hreadid=533995

User Form to Modify Row
 

in a commandbutton click module, maybe something like this for each item you
want to write back:

me.textbox1.value = sheets("sheet2").range("a12").value


Gary



"BBoner" wrote in message
...

I have created a user form that collects information from a worksheet
and puts it into text boxes. I did this with a list box that has a
named range as a source.

Now that I have the information in the text boxes I will only allow a
couple of them to be modified. Once they are modified I need to
overwrite them to the row based on the value of one of the cells. It is
actually a quote number that is in the first row.

One more thing, the worksheet is not active.

How do I set a row active by a value of a text box?

Thank You,


--
BBoner
------------------------------------------------------------------------
BBoner's Profile:
http://www.excelforum.com/member.php...fo&userid=5908
View this thread: http://www.excelforum.com/showthread...hreadid=533995

User Form to Modify Row
 

Thank you,

I used the following in a data entry form:


Private Sub cbAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("OpenQuotes")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row

'check for a quote number
If Me.txtQuote.Value = "" Then
Me.txtQuote.SetFocus
msgbox "Enter Quote Number"
Exit Sub
End If
If Me.txtCustomer.Value = "" Then
Me.txtCustomer.SetFocus
msgbox "Enter Customer"
Exit Sub
End If
If Me.cboProbability.Value = "" Then
Me.cboProbability.SetFocus
msgbox "Enter Probability"
Exit Sub
End If
If Me.txtQty.Value = "" Then
Me.txtQty.SetFocus
msgbox "Enter Quantity"
Exit Sub
End If
If Me.cboBoom.Value = "" Then
msgbox "Select Model & Enter Boom Version"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtQuote.Value
ws.Cells(iRow, 2).Value = Me.txtQdate.Value
ws.Cells(iRow, 3).Value = Me.txtCustomer.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value
ws.Cells(iRow, 5).Value = Me.cboModel.Value
ws.Cells(iRow, 6).Value = Me.cboBoom.Value
ws.Cells(iRow, 8).Value = Me.cboRemote.Value
ws.Cells(iRow, 7).Value = Me.obWinch.Value
ws.Cells(iRow, 9).Value = Me.txtCdate.Value
ws.Cells(iRow, 10).Value = Me.cboProbability.Value


'clear the data
Me.txtQuote.Value = ""
Me.txtCustomer.Value = ""
Me.cboModel.Value = ""
Me.txtQdate.Value = ""
Me.txtCdate.Value = ""
Me.txtCustomer.Enabled = False
Me.cboBoom.Value = ""
Me.obWinch.Value = False

Me.cboProbability.Value = ""
Me.cboProbability.Enabled = False
Me.cboBoom.Enabled = False
Me.cboRemote.Value = False
Me.cboRemote.Enabled = False
Me.txtQuote.SetFocus
cbClose.Enabled = True
End Sub
It found the last row and inserted each value in the specified cells.

I want to do the same thing with the modification form but instead of
using the last empty row I want to use the row begining with the value
of the text box txtquote.value


--
BBoner
------------------------------------------------------------------------
BBoner's Profile: http://www.excelforum.com/member.php...fo&userid=5908
View this thread: http://www.excelforum.com/showthread...hreadid=533995

User Form to Modify Row
 

so, are you saying you want to overwrite this value
ws.Cells(iRow, 1).Value = Me.txtQuote.Value

with whatever is changed on the form?

irow was the last empty row, so if you're still in the same for, irow should be
the same.

but if you want to use the statement again,
iRow = ws.Cells(Rows.Count, 1). End(xlUp).Offset(1, 0).Row


just lose the offset

irow = Cells(Rows.Count, 1).End(xlUp).Row

would give you the last row of data

then

Me.txtQuote.Value = ws.Cells(iRow, 1).Value

if you don't want to reuse the irow statement, maybe this

Me.txtQuote.Value = ws.Cells(iRow -1, 1).Value


other than that, not sure what you want. maybe someone does.

--


Gary


"BBoner" wrote in message
...

Thank you,

I used the following in a data entry form:


Private Sub cbAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("OpenQuotes")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
End(xlUp).Offset(1, 0).Row

'check for a quote number
If Me.txtQuote.Value = "" Then
Me.txtQuote.SetFocus
msgbox "Enter Quote Number"
Exit Sub
End If
If Me.txtCustomer.Value = "" Then
Me.txtCustomer.SetFocus
msgbox "Enter Customer"
Exit Sub
End If
If Me.cboProbability.Value = "" Then
Me.cboProbability.SetFocus
msgbox "Enter Probability"
Exit Sub
End If
If Me.txtQty.Value = "" Then
Me.txtQty.SetFocus
msgbox "Enter Quantity"
Exit Sub
End If
If Me.cboBoom.Value = "" Then
msgbox "Select Model & Enter Boom Version"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtQuote.Value
ws.Cells(iRow, 2).Value = Me.txtQdate.Value
ws.Cells(iRow, 3).Value = Me.txtCustomer.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value
ws.Cells(iRow, 5).Value = Me.cboModel.Value
ws.Cells(iRow, 6).Value = Me.cboBoom.Value
ws.Cells(iRow, 8).Value = Me.cboRemote.Value
ws.Cells(iRow, 7).Value = Me.obWinch.Value
ws.Cells(iRow, 9).Value = Me.txtCdate.Value
ws.Cells(iRow, 10).Value = Me.cboProbability.Value


'clear the data
Me.txtQuote.Value = ""
Me.txtCustomer.Value = ""
Me.cboModel.Value = ""
Me.txtQdate.Value = ""
Me.txtCdate.Value = ""
Me.txtCustomer.Enabled = False
Me.cboBoom.Value = ""
Me.obWinch.Value = False

Me.cboProbability.Value = ""
Me.cboProbability.Enabled = False
Me.cboBoom.Enabled = False
Me.cboRemote.Value = False
Me.cboRemote.Enabled = False
Me.txtQuote.SetFocus
cbClose.Enabled = True
End Sub
It found the last row and inserted each value in the specified cells.

I want to do the same thing with the modification form but instead of
using the last empty row I want to use the row begining with the value
of the text box txtquote.value


--
BBoner
------------------------------------------------------------------------
BBoner's Profile:
http://www.excelforum.com/member.php...fo&userid=5908
View this thread: http://www.excelforum.com/showthread...hreadid=533995

User Form to Modify Row
 

Thak You,

This wasn't quite what I was looking for either.

Also I forgot to mention I am very new at this.

I am trying to chane the data in the row that starts with my quote
number in the first column.

I thought I could do this tha same way the input form I created does
but by the iRow the one that has the quote number in the first column.

I'm sorry if I am not beeing clear enough, I am inorant or the proper
terms I should be using.

Thank you again for any help.


--
BBoner
------------------------------------------------------------------------
BBoner's Profile: http://www.excelforum.com/member.php...fo&userid=5908
View this thread: http://www.excelforum.com/showthread...hreadid=533995