Kelly05201
New Member
- Joined
- Jun 17, 2016
- Messages
- 29
Hi all...
Ever get burnt out? The more code you write, the further you are from your objective? I knew you'd understand.
Here's the fast summary of this userform action:
Check to see if a workbook exists -
If it exists, is it open? If not open, open it. Once open, Worksheet("Registration").Activate
Then copy some userform values to the last line.
If the workbook doesn't exists, toss up a message box and end the sub.
No biggie, right? My code is sooooo bloated and still doesn't work!
Thank you soooooo much in advance!
Ever get burnt out? The more code you write, the further you are from your objective? I knew you'd understand.
Here's the fast summary of this userform action:
Check to see if a workbook exists -
Code:
fileName:="C:\Users\admin\desktop\KELLY'S CRAP\cambridge\Autosaves\Event " & Format(Date, yyyymmdd) & ".xlsm"
Then copy some userform values to the last line.
If the workbook doesn't exists, toss up a message box and end the sub.
No biggie, right? My code is sooooo bloated and still doesn't work!
Code:
Dim wBook As Workbook
On Error Resume Next
Set wBook = Workbooks("Event " & Format(Date, yyyymmdd) & ".xlsm")
If FileThere("C:\Users\admin\Desktop\KELLY'S CRAP\cambridge\Autosaves\Event " & Format(Date, yyyymmdd) & ".xlsm") Then
MsgBox ("found the magic file")
If wBook Is Nothing Then 'Not open
MsgBox ("Workbook exists, but is not open")
Set wBook = Nothing
On Error GoTo 0
Workbooks.Open _
FileName:="C:\Users\admin\desktop\KELLY'S CRAP\cambridge\Autosaves\Event " & Format(Date, yyyymmdd) & ".xlsm", _
ReadOnly:=False
Else 'It is open
Dim EVFNAME As String
EVFNAME = "Event " & Format(Date, yyyymmdd) & ".xlsm"
MsgBox (EVFNAME & " is currently open")
Workbooks(EVFNAME).Activate
End If
' IT'S RIGHT ABOUT HERE WHERE THE CODE FALLS ON ITS FACE, but the rest is silly bloated too.
Dim wst As Worksheet
Dim iRow As Long
Set wst = ActiveWorkbook.Sheets("Registration")
Sheets(wst).Activate
'find first empty row in database
iRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'copy the userform values to the event sheet that reflects today's date
Cells(iRow, 1).Value = Me.FIRSTNAME.Value
Cells(iRow, 2).Value = Me.LASTNAME.Value
Else
MsgBox ("There is no book created for today's event. In order to add this person to today's event, the workbook must exist FIRST.")
End If
Application.ScreenUpdating = True
Unload Me
End Sub
Thank you soooooo much in advance!