austenr
04-22-2005, 01:45 PM
I need to incorporate the following code into the zip file below. It will allow me to edit the database.
Option Explicit
Dim iRowNumber As Integer
Private Sub btnRestore_Click()
Call GetData
btnRestore.Enabled = False
End Sub
Private Sub CommandButton1_Click()
Call PutData
Range("DataBase").Resize(Range("Database").Rows.Count + 1).Name = "Database"
iRowNumber = Range("Database").Rows.Count
Call GetData
TextA.SetFocus
ScrollBar1.Max = Range("Database").Rows.Count
ScrollBar1.Value = iRowNumber
btnRestore.Enabled = False
lblRecNumber.Caption = iRowNumber - 1
End Sub
Private Sub CommandButton2_Click()
If Range("Database").Rows.Count = 2 Then
MsgBox "You cannot delete last record", vbExclamation
Exit Sub
End If
If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbOKCancel) = vbCancel Then Exit Sub
Range("DataBase").Rows(iRowNumber).EntireRow.Delete
If iRowNumber > Range("Database").Rows.Count Then
iRowNumber = Range("Database").Rows.Count
End If
Call GetData
ScrollBar1.Enabled = False
ScrollBar1.Value = iRowNumber
ScrollBar1.Max = Range("Database").Rows.Count
lblRecNumber.Caption = iRowNumber - 1
End Sub
Private Sub CommandButton4_Click()
Unload Me
End Sub
Private Sub ScrollBar1_Change()
Call PutData
iRowNumber = ScrollBar1.Value
btnRestore.Enabled = False
Call GetData
TextA.SetFocus
lblRecNumber.Caption = iRowNumber - 1
End Sub
Private Sub TextA_Change()
Me.EditEntry
End Sub
Private Sub TextB_Change()
Me.EditEntry
End Sub
Private Sub TextC_Change()
Me.EditEntry
End Sub
Private Sub TextD_Change()
Me.EditEntry
End Sub
Private Sub TextE_Change()
Me.EditEntry
End Sub
Private Sub UserForm_Activate()
iRowNumber = Range("lastRecordNumber")
If iRowNumber > Range("Database").Rows.Count Then iRowNumber = Range("Database").Rows.Count
Call GetData
ScrollBar1.Value = iRowNumber
ScrollBar1.Max = Range("Database").Rows.Count
btnRestore.Enabled = False
End Sub
Sub EditEntry()
btnRestore.Enabled = True
End Sub
Sub GetData()
With Me
.TextA = Range("DataBase").Cells(iRowNumber, 1)
.TextB = Range("DataBase").Cells(iRowNumber, 2)
.TextC = Range("DataBase").Cells(iRowNumber, 3)
.TextD = Range("DataBase").Cells(iRowNumber, 4)
.TextE = Range("DataBase").Cells(iRowNumber, 5)
End With
End Sub
Sub PutData()
With Me
Range("DataBase").Cells(iRowNumber, 1) = .TextA
Range("DataBase").Cells(iRowNumber, 2) = .TextB
Range("DataBase").Cells(iRowNumber, 3) = .TextC
Range("DataBase").Cells(iRowNumber, 4) = .TextD
Range("DataBase").Cells(iRowNumber, 5) = .TextE
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call PutData
Range("lastRecordNumber") = iRowNumber
End Sub
Thanks to everyone that has helped me on this project.
Option Explicit
Dim iRowNumber As Integer
Private Sub btnRestore_Click()
Call GetData
btnRestore.Enabled = False
End Sub
Private Sub CommandButton1_Click()
Call PutData
Range("DataBase").Resize(Range("Database").Rows.Count + 1).Name = "Database"
iRowNumber = Range("Database").Rows.Count
Call GetData
TextA.SetFocus
ScrollBar1.Max = Range("Database").Rows.Count
ScrollBar1.Value = iRowNumber
btnRestore.Enabled = False
lblRecNumber.Caption = iRowNumber - 1
End Sub
Private Sub CommandButton2_Click()
If Range("Database").Rows.Count = 2 Then
MsgBox "You cannot delete last record", vbExclamation
Exit Sub
End If
If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbOKCancel) = vbCancel Then Exit Sub
Range("DataBase").Rows(iRowNumber).EntireRow.Delete
If iRowNumber > Range("Database").Rows.Count Then
iRowNumber = Range("Database").Rows.Count
End If
Call GetData
ScrollBar1.Enabled = False
ScrollBar1.Value = iRowNumber
ScrollBar1.Max = Range("Database").Rows.Count
lblRecNumber.Caption = iRowNumber - 1
End Sub
Private Sub CommandButton4_Click()
Unload Me
End Sub
Private Sub ScrollBar1_Change()
Call PutData
iRowNumber = ScrollBar1.Value
btnRestore.Enabled = False
Call GetData
TextA.SetFocus
lblRecNumber.Caption = iRowNumber - 1
End Sub
Private Sub TextA_Change()
Me.EditEntry
End Sub
Private Sub TextB_Change()
Me.EditEntry
End Sub
Private Sub TextC_Change()
Me.EditEntry
End Sub
Private Sub TextD_Change()
Me.EditEntry
End Sub
Private Sub TextE_Change()
Me.EditEntry
End Sub
Private Sub UserForm_Activate()
iRowNumber = Range("lastRecordNumber")
If iRowNumber > Range("Database").Rows.Count Then iRowNumber = Range("Database").Rows.Count
Call GetData
ScrollBar1.Value = iRowNumber
ScrollBar1.Max = Range("Database").Rows.Count
btnRestore.Enabled = False
End Sub
Sub EditEntry()
btnRestore.Enabled = True
End Sub
Sub GetData()
With Me
.TextA = Range("DataBase").Cells(iRowNumber, 1)
.TextB = Range("DataBase").Cells(iRowNumber, 2)
.TextC = Range("DataBase").Cells(iRowNumber, 3)
.TextD = Range("DataBase").Cells(iRowNumber, 4)
.TextE = Range("DataBase").Cells(iRowNumber, 5)
End With
End Sub
Sub PutData()
With Me
Range("DataBase").Cells(iRowNumber, 1) = .TextA
Range("DataBase").Cells(iRowNumber, 2) = .TextB
Range("DataBase").Cells(iRowNumber, 3) = .TextC
Range("DataBase").Cells(iRowNumber, 4) = .TextD
Range("DataBase").Cells(iRowNumber, 5) = .TextE
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call PutData
Range("lastRecordNumber") = iRowNumber
End Sub
Thanks to everyone that has helped me on this project.