change a form from input new data to edit existing data

glossy001

New Member
Joined
Sep 18, 2014
Messages
13
can any body help to change this form which works perfectly for inputting new data into a spread sheet to being able to edit the existing data in the spread sheet,scroll through the spread sheet to check existing data and still be able to add new data.
Many thanks in advance.

Private Sub CmdBtnEnterData_Click()

'Dim RowCount As 0
Dim RowCount As Long
Dim ctl As Control

'check user input if no input show message box
Select Case True

'removed this piece of code when adding the calendar control
'Case Me.TxtBxDate.Value = ""
'MsgBox "please enter the date"
'Me.TxtBxDate.SetFocus

Case Me.TxtBxInfo.Value = ""
MsgBox "Please insert the details of the task as completed "
Me.TxtBxInfo.SetFocus


Case Me.CmboBxSite.Value = ""
MsgBox "Insert details of your location when the task was completed "
Me.CmboBxSite.SetFocus


Case Me.CmboBxProjTitle.Value = ""
MsgBox "Insert a project title for the task "
Me.CmboBxProjTitle.SetFocus

Case Me.CmboBxJob.Value = ""
MsgBox "what sort of work was this "
Me.CmboBxJob.SetFocus


Case Me.CmboBxServiceObj.Value = ""
MsgBox "The service objective this task falls under "
Me.CmboBxServiceObj.SetFocus


'CmboBxObjectives
Case Me.CmboBxObjectives.Value = ""
MsgBox "Description "
Me.CmboBxObjectives.SetFocus




'CmboBxActionSteps
Case Me.CmboBxActionSteps.Value = ""
MsgBox "add the steps taken to achieve this objective "
Me.CmboBxActionSteps.SetFocus


'CmboBxExpPerform
Case Me.CmboBxExpPerform.Value = ""
MsgBox "Performance value "
Me.CmboBxExpPerform.SetFocus

'supporting documentation box not neccessary to check the input
'TxtBxSupportDoc
'Case Me.TxtBxSupportDoc.Value = ""
'MsgBox "Add any supporting documentation "
'Me.TxtBxSupportDoc.SetFocus


Case Else
'Entries validated
'note the space on the end of Data
With Worksheets("Data ").Range("A" & Rows.Count).End(xlUp)

.Offset(1, 0).Value = Me.Calendar.Value
.Offset(1, 1).Value = Me.TxtBxInfo.Value
.Offset(1, 2).Value = Me.CmboBxSite.Value
.Offset(1, 3).Value = Me.CmboBxProjTitle.Value
.Offset(1, 4).Value = Me.CmboBxJob.Value
.Offset(1, 5).Value = Me.CmboBxServiceObj.Value
.Offset(1, 6).Value = Me.CmboBxObjectives.Value
.Offset(1, 7).Value = Me.CmboBxActionSteps.Value
.Offset(1, 8).Value = Me.CmboBxExpPerform.Value
.Offset(1, 9).Value = Me.CmboBxExceedPer.Value
.Offset(1, 10).Value = Me.TxtBxSupportDoc.Value
End With

'clear the form

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = ""
Next ctl

End Select

End Sub
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Watch MrExcel Video

Forum statistics

Threads
1,108,851
Messages
5,525,213
Members
409,634
Latest member
Gov5225

This Week's Hot Topics

Top