Userforms and editing current records

[Guest]

I've designed a simple userform in Excel VBA to insert new records into a
worksheet. It works fine, however there are a couple of things I would like
to do with it:


1) I would like it to report back in a message box the value of another
cell, say the A2, where the userform inserts data into B2 thro' to D2, with
A2 made up of a concatenation of B2 and C2 (to make up a ref Nos).

2) I would like the userform to be able to edit previous entries by clicking
on the previous or next button, or searching on one of the data entry fields,
say A2 - the ref Nos.

Can you help.

Tom

 

[Guest]

Post your code for suggested modifications. To do what you want would
required knowing the row where you wrote the data. In advance, assume that
was indicated by the variable rw

msgbox cells(rw,"A").Value

assuming the concatentation is performed by a pre entered formula.

to edit data, assume a variable rw stores the "current" row

Textbox1.Text = cells(rw,2)
Textbox2.Text = Cells(rw,3)
Textbox3.Text = cells(rw,4)

populates the cells. Use a button or spinbutton to increment/decrement rw
and execute code like the above. Writing back is the same as you are doing
now, only you want to work on the "rw" you were editing.

 

[Guest]

Here is the code for thew userform.

' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' This is the command to close this userform and open up the main
' start menu.
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Private Sub CmdMenu_Click()
Unload Me
Start.Show


End Sub





' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' This is the command to save the document and leave the form open
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Private Sub CmdSave_Click()
If MsgBox("Do you want to continue ?", vbOKCancel, _
"Job Index - Save") = vbCancel Then Exit Sub

Dim WB As Workbook
For Each WB In Workbooks
WB.Save
Next WB
Application.StatusBar = "All Workbooks Saved."

End Sub

' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' This is the command to save the document and EXIT the form
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Private Sub CmdSaveExit_Click()
If MsgBox("Do you want to continue ?", vbOKCancel, _
"Job Index - Save and Exit") = vbCancel Then Exit Sub

Application.Quit
' MsgBox "Do you want to save and exit?", vbExclamation, "Job Index"
' Answer = MsgBox("Do you want to continue ?", vbYesNo)
' messagebox with YES- and NO-buttons,
' the result is an integer, the constants are named vbYes and vbNo.
ThisWorkbook.Close SaveChanges:=True
End Sub

' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' This is the command to clear the form and leave it open
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

' Private Sub cmdClearForm_Click()
' End Sub

' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' This is the command to CANCEL the form and close it
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Private Sub CmdCancel_Click()
If MsgBox("Do you want to continue? This will close without adding to
database or saving.", vbOKCancel, _
"Job Index - Close") = vbCancel Then Exit Sub
Unload Me
' MsgBox "Do you want to cancel?", vbExclamation, "Job Index"
End Sub

' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' This is the command to complete the form and leave it open
' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control

' Checks User inputs. This series of commands checks that the user inputs
' the required details in the boxes

' Date Received

If Me.TxtDateRecd.Value = "" Then
MsgBox "The received date box must be completed.", vbExclamation, "Job
Index"
Me.TxtDateRecd.SetFocus
Exit Sub
End If

' Correcting date received details using date format

If Not IsDate(Me.TxtDateRecd.Value) Then
MsgBox "The received date box must contain a date in the DD/MM/YY
format.", vbExclamation, "Job Index"
Me.TxtDateRecd.SetFocus
Exit Sub
End If

' This is for a reference Number

If Me.TxtRefNos.Value = "" Then
MsgBox "The Reference number of the job must be completed.",
vbExclamation, "Job Index"
Me.TxtRefNos.SetFocus
Exit Sub
End If

' Description of job

If Me.TxtDesc.Value = "" Then
MsgBox "The description of the job box must be completed.",
vbExclamation, "Job Index"
Me.TxtDesc.SetFocus
Exit Sub
End If

' Location of job

If Me.TxtLoc.Value = "" Then
MsgBox "The description of the location box must be completed.",
vbExclamation, "Job Index"
Me.TxtLoc.SetFocus
Exit Sub
End If

' Workers details using works number (3 digits) only

If Me.TxtWorker.Value = "" Then
MsgBox "The workers works number must be completed.", vbExclamation,
"Job Index"
Me.TxtWorker.SetFocus
Exit Sub
End If

' Correcting workers details using works number (3 digits) only

If Not IsNumeric(Me.TxtWorkerr.Value) Then
MsgBox "The workers works number must be completed using 3 digits.",
vbExclamation, "Job Index"
Me.TxtWorker.SetFocus
Exit Sub
End If


' Date Due

If Me.TxtDue.Value = "" Then
MsgBox "The due date box must be completed.", vbExclamation, "Job Index"
Me.TxtDue.SetFocus
Exit Sub
End If

' Correcting date due details using date format

If Not IsDate(Me.TxtDue.Value) Then
MsgBox "The due date box must contain a date in the DD/MM/YY format.",
vbExclamation, "Job Index"
Me.TxtDue.SetFocus
Exit Sub
End If


' Write data to worksheet

RowCount = Worksheets("2007").Range("B5").CurrentRegion.Rows.Count
With Worksheets("2007").Range("B5")


.Offset(RowCount, 1).Value = Me.TxtDateRecd.Value
.Offset(RowCount, 0).Value = Me.TxtRefNos.Value
.Offset(RowCount, 2).Value = Me.TxtDesc.Value
.Offset(RowCount, 4).Value = Me.TxtLoc.Value
.Offset(RowCount, 5).Value = Me.CboStation.Value
.Offset(RowCount, 6).Value = Me.TxtDue.Value
.Offset(RowCount, 21).Value = Me.TxtWorker.Value

End With



' This command is for selecting the type of job via a radio button
' By default it will insert other
If OptSBD = True Then
ActiveCell.Offset(0, 3).Value = "A Type"
ElseIf OptALO = True Then
ActiveCell.Offset(0, 3).Value = "B Type"
ElseIf OptCCTV = True Then
ActiveCell.Offset(0, 3).Value = "C Type"
ElseIf OptComm = True Then
ActiveCell.Offset(0, 3).Value = "D Type"
ElseIf OptDom = True Then
ActiveCell.Offset(0, 3).Value = "E Type"
ElseIf OptCPcom = True Then
ActiveCell.Offset(0, 3).Value = "F Type"
ElseIf OptCPdom = True Then
ActiveCell.Offset(0, 3).Value = "G Type"
Else
ActiveCell.Offset(0, 3).Value = "OTHer"
End If

' Call UserForm_Initialize
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl


End Sub


Private Sub UserForm_Initialize()

TxtDateRecd.Value = ""
TxtRefNos.Value = ""
TxtLoc.Value = ""
TxtDue.Value = ""
TxtWorker.Value = ""
CboStation.Value = ""
TxtDateRecd.SetFocus

End Sub

 

[Guest]

Tom,

To do what I want would require the command to identify the row (which would
be the last one completed - I'm using the range and offset to input the
detail) and the column. The concatentation cell is in column "P" and is
worked out using the concatentation of various cells including the workers
Nos, location and type of work.

When it comes to the editing I want to use the input form to edit previously
inserted data. Ideally I would like the concatentation cell value shown on
this form as well, but I haven't been able to work it out.

I've got to admit that i am fairly new to programming and any assistance
would be appreciated.

Tom