Using VBA originally written for Excel Workbook in Access

[AccessRookee]

Hello All,

As my username suggests, I am a complete novice when it comes to access. I am fairly proficient with excel and have been using an excel workbook as a sort of sign up sheet. It has worked well up until recently. I believe the problem is the number of users. When I first developed it, we had 2 to maybe as many as 6 users at a time. Now we can have up to 15 users at a given time, which is causing problems with corrupted files or files being deleted all together. Someone suggested using access to accomplish the same thing so here I am.

Let me first explain what my spread sheet does. The user opens the file, which is in date format, i.e. 12092014.xlsm. This launches a simple user form that displays the users name and 3 buttons. An exit button, and 2 buttons that assign either one 5 minute block of time or two 5 minute blocks of time. The workbook enters the user name, date, and time the block(s) are assigned. I use a MsgBox to display the time values to the user. Once the user clicks ok, the workbook saves and closes. When the last block of the day is assigned (23:55), the next user that requests a block triggers the spreadsheet to create a new file with the next date, i.e. 12102014, clears out all of the cells, and assigns time 00:00. All of this is accomplished using VBA. I know access uses VBA as well but I'm really not sure where to begin. Is my code even usable or should I start from scratch?

Any and all help would be greatly appreciated. I will upload my exsisting spreadsheet so you can see exactly how it works.

Thank You,

[June7]

How can I open the workbook and get to the code editor? If I close the form, the workbook closes. I can't get to the ribbon while the form is open. A catch-22 situation. Okay, I figured that out - open the code editor before opening the file. Not seeing much code, certainly not anything that would save input to sheet. I don't even see code for the Exit button. Okay, one more hurdle cleared - right click on the form and select Show Code. I've never built form in Excel.

Most of the code would be total rewrite or not even needed. Instead of referencing sheets and cells, would probably just enter data into textboxes bound to table. Good news is there isn't much code.

However, before even thinking about code, need to get the data structure defined. If you need to learn relational database concepts, here is one place to start: http://www.rogersaccesslibrary.com/

[AccessRookee]

cntl+break will stop the code. Most of the code resides in the user form. click on the user form then the Get a Block button to view the code.

[AccessRookee]

Here is the code:

Option Explicit


Private Sub cbExit_Click()
ThisWorkbook.Close SaveChanges:=True
End Sub


Private Sub cbGet2Blocks_Click()
Dim lastRow As Long
Dim myPath As String
Dim myName As String
Dim newName As String
Dim FirBlock As String
Dim SecBlock As String
Dim z As Integer
Dim A3 As String




A3 = "0:00"
z = 1
myPath = ThisWorkbook.Path & "\"
myName = ThisWorkbook.Name




Application.ScreenUpdating = False
Restart:


Do


With ThisWorkbook.Worksheets("Sheet1")
'Find the last used row
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

'Check if last row reached
If lastRow >= 290 Then
'Save current version, and then...
ThisWorkbook.Save
'Create a new file with next date
newName = Format(DateSerial(Mid(myName, 5, 4), Mid(myName, 1, 2), Mid(myName, 3, 2)) + 1, "mmddyyyy")
.Range("B3290").ClearContents
Application.DisplayAlerts = False
ThisWorkbook.SaveAs myPath & newName & ".xlsm"
Application.DisplayAlerts = True
GoTo Restart
End If

z = z + 1

'Go to next blank row
lastRow = lastRow + 1
'retrive info from sheet
FirBlock = Format(.Cells(lastRow - 1, "A").Value, "h:mm & ")
SecBlock = Format(.Cells(lastRow, "A").Value, "h:mm")
'Stamp in new information
.Cells(lastRow, "B").Value = Me.tbName
.Cells(lastRow, "C").Value = Time
.Cells(lastRow, "D").Value = Date



End With


Loop While z < 3


Application.ScreenUpdating = True
If SecBlock = A3 Then
MsgBox "You Got Blocks: 23:55 & 0:00", vbOKOnly, "Blocks Assigned"
Call cbExit_Click
End If

MsgBox "You Got Blocks: " & FirBlock & SecBlock, vbOKOnly, "Blocks Assigned"


Call cbExit_Click
End Sub


Private Sub cbGetBlock_Click()
Dim lastRow As Long
Dim myPath As String
Dim myName As String
Dim newName As String
Dim Block As String


myPath = ThisWorkbook.Path & "\"
myName = ThisWorkbook.Name




Application.ScreenUpdating = False
Restart:
With ThisWorkbook.Worksheets("Sheet1")
'Find the last used row
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

'Check if last row reached
If lastRow >= 290 Then
'Save current version, and then...
ThisWorkbook.Save
'Create a new file with next date
newName = Format(DateSerial(Mid(myName, 5, 4), Mid(myName, 1, 2), Mid(myName, 3, 2)) + 1, "mmddyyyy")
.Range("B3290").ClearContents
Application.DisplayAlerts = False
ThisWorkbook.SaveAs myPath & newName & ".xlsm"
Application.DisplayAlerts = True
GoTo Restart
End If

'Go to next blank row
lastRow = lastRow + 1
'retrive info from sheet
Block = Format(.Cells(lastRow, "A").Value, "h:mm")
'Stamp in new information
.Cells(lastRow, "B").Value = Me.tbName
.Cells(lastRow, "C").Value = Time
.Cells(lastRow, "D").Value = Date

End With
Application.ScreenUpdating = True
MsgBox "You Got Block: " & Block, vbOKOnly, "Block Assigned"
Call cbExit_Click
End Sub


Private Sub tbBlock_Change()


End Sub


Private Sub tbName_Change()


End Sub




Private Sub UserForm_Initialize()
Me.tbName = Application.UserName
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call cbExit_Click
End Sub

[June7]

You probably read my post before I did some edits. Might look at again.