Xl VBA Form

[atuljadhav]

i have created VBA form
With Party name, location & Date
how can i change prev. record after submit the details or how i find my prev entry

the data sheet is locked for user

 

[atuljadhav]

help

 

[Trevor G]

can you show some data from your spreadsheet and also show what code you have so far.

 

[atuljadhav]

This is code to save our record

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> To make the Excel UserForm buttons perform an action, you create code that runs when the button is clicked.


Add code to the cmdAdd button

Private Sub cmdAdd_Click()</pre>
Dim iRow As Long</pre>
Dim ws As Worksheet</pre>
Set ws = Worksheets("PartsData")</pre>
</pre>
'find first empty row in database</pre>
iRow = ws.Cells(Rows.Count, 1) _</pre>
.End(xlUp).Offset(1, 0).Row</pre>
</pre>
'check for a part number</pre>
If Trim(Me.txtPart.Value) = "" Then</pre>
Me.txtPart.SetFocus</pre>
MsgBox "Please enter a part number"</pre>
Exit Sub</pre>
End If</pre>
</pre>
'copy the data to the database</pre>
ws.Cells(iRow, 1).Value = Me.txtPart.Value</pre>
ws.Cells(iRow, 2).Value = Me.txtLoc.Value</pre>
ws.Cells(iRow, 3).Value = Me.txtDate.Value</pre>
ws.Cells(iRow, 4).Value = Me.txtQty.Value</pre>
</pre>
'clear the data</pre>
Me.txtPart.Value = ""</pre>
Me.txtLoc.Value = ""</pre>
Me.txtDate.Value = ""</pre>
Me.txtQty.Value = ""</pre>
Me.txtPart.SetFocus</pre>
</pre>
End Sub</pre>

 

[Trevor G]

I think you have a couple of choices here.

1 you could have some additional textbox on the form to also capture the entries you are adding, then make them invisible and so you can refer to them if needed.

2 you could also use a sheet which acts like a log sheet and copy the entries to that sheet and maybe add date and time stamps to indicated when the entries where added. Allowing a search in the Log.