VBA Userform

[nelsonsix]

Hi All,

I have created a Userform for a task project I am working on for my job. I am not a programmer and have limited knowledge of excel.

In essence, I have created a form that will be used by about 15-20 people for entering tasks they have completed. The details are then inserted into a worksheet and I can use that data for monitoring workloads and productivity. The problem I can sort of foresee is when multiple users try and enter userform data at the same time.

FYI - The code I have written works perfectly for 1 user.

The code I have used when you click on the 'add' button it finds the top most empty row and enters the details in the form. If more than one person opens the shared workbook at the same time, then each user will be entering their info and it will be inputted on the same row, overwriting each others form data. This is a problem.

The VBA code is below, again, i am not an expert and do not have access to databases. Its a simple project but I want it to work properly.

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Report Entry")


'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'check if completed on xplan
If Trim(Me.chkCompleted.Value) = False Then
  Me.chkCompleted.SetFocus
  Me.lblCompleted.ForeColor = RGB(255, 0, 0) ' red
  MsgBox "Has the report been completed on XPlan?"
  Exit Sub
End If


'copy the data to the database
With ws
'  .Unprotect Password:="password"
  .Cells(lRow, 1).Value = Me.cboPara.Value
  .Cells(lRow, 2).Value = Me.cboReport.Value
  .Cells(lRow, 3).Value = Me.chkCashflow.Value
  .Cells(lRow, 4).Value = Me.txtCeding.Value
  .Cells(lRow, 5).Value = Me.chkAQ.Value
  .Cells(lRow, 6).Value = Me.chkCompleted.Value
  .Cells(lRow, 7).Value = Me.txtDate.Value
'  .Protect Password:="password"
End With


'clear the data
Me.cboPara.Value = ""
Me.cboReport.Value = ""
Me.chkCashflow.Value = False
Me.txtCeding.Value = ""
Me.chkAQ.Value = False
Me.chkCompleted.Value = False
Me.cboPara.SetFocus
Me.lblCompleted.ForeColor = RGB(0, 0, 0) ' black
txtDate.Value = Now
txtDate = Format(txtDate.Value, "dd mmmm yyyy")


End Sub


Private Sub UserForm_Initialize()
Dim cPara As Range
Dim cReport As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupSheet")


For Each cReport In ws.Range("ReportType")
    With Me.cboReport
        .AddItem cReport.Value
    End With
Next cReport


For Each cPara In ws.Range("Paraplanner")
    With Me.cboPara
        .AddItem cPara.Value
    End With
Next cPara




txtDate.Value = Now
txtDate = Format(txtDate.Value, "dd mmmm yyyy")


Me.cboPara.SetFocus


End Sub




Private Sub cmdClose_Click()
    Unload Me
End Sub

My question is, is there anyway to get around this problem with a shared workbook?

Thank you in advance for your help.
Neil

 

[dmt32]

Hi,
I think you will find most here would say avoid sharing workbooks - mainly for loss of various features but also, they seldom perform in manner intended.

Personally, I would suggest that you provide a template copy of your workbook to each of your users & have these write the data to a central workbook (database) on your network. This approach means no loss of features on users copies and they can leave their copy open all day if wanted as the master workbook is open only for the brief period user submits data. You can read from the master workbook to compile your reports.

This approach does require a little more coding to your project but is quite doable.

Others here may have alternative suggestions.

Dave

 

[nelsonsix]

Hi Dave,

Thanks so much for your response. I think this approach will work well.

Could you put me on the direction of how I would collect the data from each of the workbooks to a central master copy?

Thanks
Neil

 

[dmt32]

Hi,

I will walk you through it in parts

1 - REMOVE THE SHARE FACILITY in your data entry workbook


2 - from your data entry workbook, Move or (Make a copy) of the worksheet "Report Entry" as a new workbook.

- save the new workbook to a folder on your network your users have access with filename "ReportDatabase.xlsx"
- close the workbook

3 - Insert in the data entry workbook a worksheet & rename it "Settings"

- add an active x command button to the sheet (anywhere will do) & double click it - this takes you to the sheets code page.

- copy & paste the following to the sheets code page:

Private Sub CommandButton1_Click()
    Dim FileNames As Variant
    Dim FileFilter As String, Title As String
    Dim FilterIndex As Integer


    Title = "Locate Report Database"
    
    
    FilterIndex = IIf(Val(Application.Version) < 12, 1, 2)


    FileFilter = "Worksheets 2003 (*.xls),*.xls," & _
                 "Worksheets 2007 > (*.xlsx),*.xlsx," & _
                 "All Excel Files (*.xl*),*.xl*," & _
                 "All Files (*.*),*.*"


    FileNames = Application.GetOpenFilename(FileFilter, FilterIndex, Title, , False)


    If Not FileNames = False Then
        With Me.Cells(10, 6)
            .Value = FileNames
            .Font.Bold = False
            .Font.Size = 16
            .Columns.AutoFit
        End With
    End If
            
End Sub


Private Sub Worksheet_Activate()
    With Me.CommandButton1
        .Left = Range("C3").Left
        .Top = Range("C3").Top
        .Height = 75.5
        .Width = 143.25
        .Caption = "Locate Report Database"
        With .Font
            .Size = 14
            .Bold = True
        End With
        .WordWrap = True
    End With
End Sub

exit VBA editor & ensure that you exit design mode on the developer ribbon tab

SAVE the workbook with NEW NAME something like "Report Data Entry.xlsm" or whatever you want.



Select settings tab & press the command button (button caption will have been added) this opens the Open File Dialog - locate the database on your network & press ok. The path & file name should appear in cell c10.


This will be used by code (to be posted later) to open the database workbook.


Let me know when all done.


Dave

 

[nelsonsix]

Hi Dave,

Thank you so much for your help, that worked perfectly and has generated a directory path for the database.

Thanks
Neil

 

[dmt32]

Hi Dave,

Thank you so much for your help, that worked perfectly and has generated a directory path for the database.

Thanks
Neil

Hi,
I neglected to say that you should save the database workbook with a password to open to limit unauthorised tampering.
Also, you need to understand that at no time, can you have the database workbook open in read / read write mode whilst users need to submit data. If you do need to open it to create reports, open it READ ONLY.

Will post the updates to your form code later.

Dave

 

[dmt32]

Hi
Part 2 as follows:

Place ALL following codes in your forms code page – Ensure that you DELETE any existing code(s) with the same name.

Option Base 1


Private Sub chkCompleted_Click()
    With Me.chkCompleted
    Me.cmdAdd.Enabled = .Value
    Me.lblCompleted.ForeColor = IIf(.Value, RGB(0, 0, 0), RGB(255, 0, 0))
    End With
End Sub


Private Sub cmdAdd_Click()
    Dim lRow As Long
    Dim FileName As String
    Dim msg As Variant
    Dim DatabaseOpenPassword As String, wsDatabasePassword As String
    Dim wbReportDatabase As Workbook
    
    
'********************************************************************************************
'*******************************************SETTINGS*****************************************
    FileName = ThisWorkbook.Worksheets("Settings").Range("F10").Text
    
    DatabaseOpenPassword = ""
    
    wsDatabasePassword = ""
    
'********************************************************************************************
    
    
    On Error GoTo exitsub


'check file exists
        If Not Dir(FileName, vbDirectory) = vbNullString Then
        Application.ScreenUpdating = False
'open database
            Set wbReportDatabase = Workbooks.Open(FileName, UpdateLinks:=False, _
                                                  ReadOnly:=False, Password:=DatabaseOpenPassword, _
                                                  IgnoreReadonlyRecommended:=True)
    
                With wbReportDatabase
                    
                    With .Worksheets(1)
                      .Unprotect Password:=wsDatabasePassword
'find first empty row in database
                        lRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
                        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'post form data to the database
                        .Cells(lRow, 1).Resize(, 7).Value = Array(Me.cboPara.Value, Me.cboReport.Value, _
                                                                  Me.chkCashflow.Value, Me.txtCeding.Value, _
                                                                  Me.chkAQ.Value, Me.chkCompleted.Value, _
                                                                  DateValue(Me.txtDate.Value))
'protect database
                    If Len(wsDatabasePassword) > 0 Then .Protect Password:=wsDatabasePassword
                    End With
'save & close file
                    .Close True


                End With
'save success
                    msg = Array("Record Saved To Database", "Record Saved")
'reset form
                    ResetControls
                
            Else
'file / folder not found
                msg = Array(FileName & Chr(10) & "File Not Found", "Not Found")
                
            End If
'release object variable
        Set wbReportDatabase = Nothing
        
exitsub:
'ensure database workbook closed
        If Not wbReportDatabase Is Nothing Then wbReportDatabase.Close False
'refresh screen
        Application.ScreenUpdating = True


        If Err > 0 Then
'report errors
            MsgBox (Error(Err)), 48, "Error"
        Else
'inform user
            MsgBox msg(1), 48, msg(2)
        End If
End Sub




Sub ResetControls()
'clear the data
Me.cboPara.Value = ""
Me.cboReport.Value = ""
Me.chkCashflow.Value = False
Me.txtCeding.Value = ""
Me.chkAQ.Value = False
Me.chkCompleted.Value = False
Me.cboPara.SetFocus


txtDate = Format(Date, "dd mmmm yyyy")
End Sub


Private Sub txtDate_AfterUpdate()
    With Me.cmdAdd
    If Not IsDate(Me.txtDate.Text) Then
        .Enabled = False
        MsgBox "Invalid Date Entry", 16, "Invalid Date"
    Else
        .Enabled = True
    End If
    End With
End Sub


Private Sub UserForm_Initialize()
    Dim ws As Worksheet


    Set ws = ThisWorkbook.Worksheets("LookupSheet")


    Me.cboReport.List = ws.Range("ReportType").Value


    Me.cboPara.List = ws.Range("Paraplanner").Value
    
    txtDate = Format(Date, "dd mmmm yyyy")
    
    Call chkCompleted_Click


End Sub


Private Sub cmdClose_Click()
    Unload Me
End Sub

NOTE the Option Base 1 statement - This MUST sit at the very TOP of your forms code page OUTSIDE any procedure.

If you have added passwords to open the workbook or protect the database worksheet then you will need to include these in the settings section of the cmdAdd_Click code. Otherwise, leave as published.
I have made some changes to the workflow disabling the cmdAdd button until the completed checkbox is selected – this removes need for the msgbox prompt – but if not what you want, can always be changed back.

Save the workbook & make a test entry. Record should have been saved to the database workbook (you will need to open it to view record).

If all well, you can now distribute the workbook giving the following instructions to users:

- locate the report database on network
- save workbook to their local drive

Users can leave their copy of workbook open as long as they want.

When submitting data, likelihood of conflicts (users submitting data at exactly the same time) should be minimal but if it does prove to be an issue I do have code to manage this.
Please understand that this approach simply allows you to collect data from many workbooks to a central workbook on your network – The master workbook (database) does NOT perform in same way as a conventional database – You CANNOT have the master workbook open in read - read / write mode whilst users are submitting their data. You cannot see data being submitted in real time.

What you will need to do when creating reports, is to open the master workbook as READ ONLY or create some code to do this for you & then copy or filter required data from it to another workbook.


Good Luck

Dave

 

[nelsonsix]

Wow, thank you so much, this works flawlessly. I appreciated all your time and effort.

I owe you a beer!

Thanks again.
Neil

 

[dmt32]

Wow, thank you so much, this works flawlessly. I appreciated all your time and effort.

I owe you a beer!

Thanks again.
Neil

Hi,
glad solution worked OK for you - not too much effort needed, done a few of these for others here & just modified to suit your project.

As number of users are submitting data to master you may want to consider placing their name & date / time against record.

You can do this quite simply by modifying the code that writes to the master workbook & adding / updating as shown in RED below.

'post form data to the database
                        .Cells(lRow, 1).Resize(, 9).Value = Array(Me.cboPara.Value, Me.cboReport.Value, _
                                                                  Me.chkCashflow.Value, Me.txtCeding.Value, _
                                                                  Me.chkAQ.Value, Me.chkCompleted.Value, _
                                                                  DateValue(Me.txtDate.Value), Environ("USERNAME"), Now())

This will place the users network name & date / time in columns H & I of the master workbook.


Thanks for offer of beer - it's second one I have had this week but forum rules prevent accepting any reward.

Dave

 

[nelsonsix]

Hi again,

I have tested the workbooks on the work network today and all is working well, multiple users are entering data and its all collating in the master perfectly.

I have noticed that as you have previously mentioned, you cannot have the master database open whilst users are inputting data, but I cant seem to open it when is read-only mode either. Excel is asking do you want to open a new copy of the master template and then asking to save it. You can cancel out of this and it still adds it to the database but its a bit confusing for users.

Is there any way to have the master database open whilst users can still enter data?

Apologies for asking more questions, really appreciate all your help so far.

Thanks
Neil