Sleeper: Updating data in a spreadsheet with User Form?

[det60]

Hi,

is there anybody who can help me how to update spreadsheet rows with a user form in excel?

thanx

det

[Bob Phillips]

Hi,

is there anybody who can help me how to update spreadsheet rows with a user form in excel?

thanx

det

Probably about 100. Give some details.

[det60]

Details of my user form to copy multiple entries into DB1:

'copy the data to the DB1 
With wksPartsData
.Cells(iRow, 1).Value = Me.cboPartid.Value
.Cells(iRow, 2).Value = Me.cboPart.Value
.Cells(iRow, 3).Value = Me.txtLoc.Value
.Cells(iRow, 4).Value = Me.txtDate.Value
.Cells(iRow, 5).Value = Me.txtPrice.Value
End with

Now i need to update or change some values in DB1 by using another user form. How can the user form find the row of the entry with all the informations which has to be changed?

Thanx
det

[Bob Phillips]

Details of my user form to copy multiple entries into DB1:

'copy the data to the DB1 
With wksPartsData
.Cells(iRow, 1).Value = Me.cboPartid.Value
.Cells(iRow, 2).Value = Me.cboPart.Value
.Cells(iRow, 3).Value = Me.txtLoc.Value
.Cells(iRow, 4).Value = Me.txtDate.Value
.Cells(iRow, 5).Value = Me.txtPrice.Value
End with

Now i need to update or change some values in DB1 by using another user form. How can the user form find the row of the entry with all the informations which has to be changed?

Thanx
det

Presumably, you have identified the data to be amended in order to load it into the form textboxes. From that point you should have a row number, or some other 'handle' that you should preserve and re-use that when writing back to the DB1.

AM I on the right lines?

[det60]

The problem is how do i identify the data which is unsorted to load it into the form textboxes?

[Bob Phillips]

The problem is how do i identify the data which is unsorted to load it into the form textboxes?

I know, I get that much. But I need some help. I havde no idea what iDB1 looks like, I have no idea how you decide what to load on that second form, etc. etc. You might know, but you are not telling me (yet!).

I presume that you can create data on form 1 and it gets appended to DB1 okay. SO lets say there are 100 items on DB1, in English, how would you decide which one is to be edited in form2?

[det60]

Sorry for not explaining it properly. But you are right. Like you said there are 100 items in Db1 which means 100 rows and i realize something is wrong in one entry which is somewhere in Db1. How do i get this entry with Partid, Part, etc... into my form2?

[Bob Phillips]

Sorry for not explaining it properly. But you are right. Like you said there are 100 items in Db1 which means 100 rows and i realize something is wrong in one entry which is somewhere in Db1. How do i get this entry with Partid, Part, etc... into my form2?

Seeing as you seem to be doing a visual identification, once identifed, select that row, fire up the form, and use something like

With wksPartsData
    Me.cboPartid.Value = .Cells(ActiveCell.Row, 1).Value
    Me.cboPart.Value = .Cells(ActiveCell.Row, 2).Value
    Me.txtLoc.Value = .Cells(ActiveCell.Row, 3).Value
    Me.txtDate.Value = .Cells(ActiveCell.Row, 4).Value
    Me.txtPrice.Value = .Cells(ActiveCell.Row, 5).Value
End With

[det60]

How does this visual identification look like or can you tell me the code to get the right row?

[Bob Phillips]

How does this visual identification look like or can you tell me the code to get the right row?

We are struggling here aren't we?

The visual identification I am referring to is done by you, so you tell me. You said

and i realize something is wrong in one entry which is somewhere in Db1.

which I read as you looked at the data and saw (visual) that something was wrong. You then called up a form and you need to get that data onto the form. Somehow, we have to have a way of knowing what row it is, and yous eem to be saying you realize. Well great, but the computer won't know what you realize, it needs some information within the data.

[johnske]

Hi det, and welcome to VBAX,

xld's doing his best to help you here but you're not really giving him (or anyone else) much to go on.

Here's a suggestion, why don't you remove any sensitive data then zip and attach the workbook (use 'manage attachments' when next posting) for us to look at.

Also, when you do, give as much detail as you can about exactly what you want the code to do and its' eventual outcome.

Regards,
John

[det60]

Here comes the whole code for my Input User Form:

Option Explicit 
 
Private Sub cmdDel_Click()
'clear the data
Me.cboPart.Value = ""
Me.cboPartid.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtPrice.Value = ""
Me.txtLoc.Value = ""
Me.cboPart.SetFocus
End Sub
 
Private Sub cmdAdd_Click()
Dim iRow As Long
'find first empty row in DB1
iRow = wksPartsData.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).row
'check for a part number
If Trim(Me.cboPart.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'copy the data to the DB1
With wksPartsData
.Cells(iRow, 1).Value = Me.cboPartid.Value
.Cells(iRow, 2).Value = Me.cboPart.Value
.Cells(iRow, 4).Value = Me.txtLoc.Value
.Cells(iRow, 5).Value = Me.txtDate.Value
.Cells(iRow, 6).Value = Me.txtQty.Value
.Cells(iRow, 9).Value = Me.txtPrices.Value
End With
'copy the data to the LookupList1
With wksLookupLists
.Cells(iRow, 1).Value = Me.txtPartID.Value
.Cells(iRow, 2).Value = Me.cboPart.Value
End With
'clear the data
Me.cboPart.Value = ""
Me.cboPartid.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtPrice.Value = ""
Me.txtLoc.Value = ""
Me.cboPart.SetFocus
End Sub
 
Private Sub cmdClose_Click()
Unload Me
End Sub
 
Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cPartid As Range
 
For Each cPartid In wksLookupLists1.Range("PartIDList")
With Me.cboPartid
.AddItem cPartid.Value
.List(.ListCount - 1, 1) = cPartid.Offset(0, 1).Value
End With
Next cPartid
For Each cPart In wksLookupLists1.Range("LocationList")
With Me.cboPart
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtPrice.Value = ""
Me.txtLoc.Value = ""
Me.cboPart.SetFocus
End Sub
 
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub

After 100 entries how can i find the row number of a specific cell value in Db1 by a second User Form without searching for it myself in DB1?
The second User Form should show me PartID, Part, Date, Loc and Price from DB1 as Default in some Text Boxes.

Thanx
Det

[Justinlabenne]

Without being able to see the workbook and how the data is actually laid out, what # of textboxes are on the 2nd form, etc, etc..

Since it looks like you may have some knowledge of code by what you posted there is a workbook download on this site called "populate userform" or something close to that, download it and look over how it happens, it may help get you pointed in the right direction, but it would be quicker and easier for us to help you out if you could attach the workbook, as recreating your useform from scratch just to try and find out what going wrong for you doesn't sound like a lot of fun.

Quickest solution, attach a sample.