PDA

View Full Version : [SOLVED] Move Macro buttons to a new sheet then sort



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.

austenr
04-22-2005, 06:04 PM
I started a new thread because I did not think the last one was clear enough.

I would like to be able to have all my buttons for this workbook in a menu so the user can choose which one he wants to execute. Also, the code (see post: Put It All Together) is a routine I want to be included in the menu. It is for adding and editing records to the database.

Is it possible to have a routine that would sort the records at the end of the routine that allows additions and editing or records?

Thanks in advance for your help.

Zack Barresse
04-22-2005, 11:40 PM
Austen, I merged your two threads together. When something like this occurs, please contact the forum Moderator, who ever it may be (it shows in the bottom right hand corner of each forum), as so we can keep the forum cleaned up and orderly. :)