Alternative to UserForm

jandrews102675

New Member
Joined
Feb 20, 2015
Messages
8
Afternoon guys, I've got an issue with the current spreadsheet I'm designing.

Essentially, we're having to monitor the ad-hoc hours worked by employees (only a small number of employees, 20 max). I've got the spreadsheet set up, with their personal details in one sheet, and then another 'database' sheet set up to log any ad-hoc hours registered against the employee.

The manager of the admin team who'll be inputting the data doesn't want the admin team to input directly to the spreadsheet because of various issues with their ability. So, I've created a Front Page, where they type in the Employee Number, the date a manager has requested the employee to work, and the amount of hours (decimal not minutes and hours) they'd like the employee to work - and the manager's name. This will then return (using various formulae) the Employee's name, their contracted hours for the week they're being requested to do ad-hoc work, and any previously logged ad-hoc hours for that day/week so that we can ensure they're not exceeding the hours they're allowed to work due to visa restrictions. This is all fine, I've got this all sorted however...

I now need to get the information they've input (Employee Number, Date Required for Work, No. of Hours Required and Manager) into the 'database' spreadsheet without the admin staff going into the sheet and typing it in. Initially I was going to use a UserForm to populate the 'database' sheet for ad-hoc hours but this would then require typing the information they've already input again, any ideas how I could remove the need to re-input the information and just move the information they've already input into the 'Home Page' to the 'database' sheet'

Summary (Tl;dr): I've got a spreadsheet, three sheets, 'Home Page', 'Personal Info' and 'Ad-hoc Hours'... our admin staff need to check ad-hoc hours using the 'Home Page' but the info they input into this sheet needs to go into 'Ad-hoc Hours' - I need this to happen without them going into the other sheets and re-typing - any VBA that'll move it across?

I know the answer is most likely going to be VBA, but I've little experience with writing in VBA so any advice would be greatly appreciated.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi,
You can use VBA to place data from your input sheet to your database sheet.

If you let me have the Input Ranges of your Data entry sheet (Home Page) in the order values appear in your database e.g Input sheet Cell D10 goes in Database Sheet (Ad-Hoc) Column A I will put some code together for you.

Dave
 
Upvote 0
Morning,

Thanks for your help, in terms of input ranges:

'Home Page H3' goes into Column A in 'Adhoc Hours'
'Home Page H9' goes into Column B in 'Adhoc Hours'
'Home Page H10' goes into Column C in 'Adhoc Hours'
'Home Page H11' goes into Column D in 'Adhoc Hours'

Any help is much appreciated!

Jake
 
Upvote 0
Morning,

Thanks for your help, in terms of input ranges:

'Home Page H3' goes into Column A in 'Adhoc Hours'
'Home Page H9' goes into Column B in 'Adhoc Hours'
'Home Page H10' goes into Column C in 'Adhoc Hours'
'Home Page H11' goes into Column D in 'Adhoc Hours'

Any help is much appreciated!

Jake

Hi,
I also meant to ask if possible to place a copy of your workbook (blank or dummy data) in a dropbox with link to it but if not, can you also provide field names for those cells & does one have a unique entry like reference number?

Dave
 
Upvote 0
Upvote 0
Hi,
Input ranges shown in blue on Home sheet are:

1 - H3,H8,H9,H10 - please confirm as these are different to your post

2 - do you want to add record no, date & time fields in database to reduce / filter out possible duplicate entries?

Dave
 
Upvote 0
Hi,
Input ranges shown in blue on Home sheet are:

1 - H3,H8,H9,H10 - please confirm as these are different to your post

2 - do you want to add record no, date & time fields in database to reduce / filter out possible duplicate entries?

Dave

Yes, while I've been adding to the Home page this morning I've moved them around so H3,H8,H9, and H10 are the input ranges.

I was considering a unique ID for each record that's input, maybe for future updates to the sheet which adds functionality to edit/delete rows of data but I doubt the requirements will ever call for it to be that advanced.With regards to the duplicate entries, I've added a table on the right of the home page to show any previously recorded 'adhoc hours' so I'm hoping the admins inputting into the spreadsheet have enough common sense to have a look through that for any matching dates/commissioning managers before they save their line. So we could add Record No but I doubt we'll need any date & time fields for the input.

Jake
 
Upvote 0
Ok leave it as it is for now.

See if following goes in right direction:

1 - From the Developer Tab Place an ActiveX CommandButton on your Home worksheet

1-1 Double click the button will take you to sheets code page & create a code tag as follows:

Code:
Private Sub CommandButton1_Click()


End Sub

delete this code tag

1- 2 from properties window on the left Change button Caption to "Submit"


2 - Place following code in the code page:

Code:
Private Sub CommandButton1_Click()
    Dim Item As Range, DataEntry As Range
    Dim Data() As Variant
    Dim CellCount As Long, i As Integer
    Dim DataRow As Long
    Dim wsAdhocHours As Worksheet


    On Error GoTo myerror
    
    Set wsAdhocHours = ThisWorkbook.Worksheets("AdhocHours")
    
    Set DataEntry = ThisWorkbook.Worksheets("Home").Range("H3,H8:H10")
    CellCount = DataEntry.Cells.Count
    
    Set DataEntry = DataEntryRange(CellCount)
    
    With Application
        .Calculation = xlCalculationManual: .EnableEvents = False: .ScreenUpdating = False
    End With


    For Each Item In DataEntry
    If Len(Item.Value) = 0 Then MsgBox "Complete All Fields", 16, "Entry Required": Item.Select: Exit Sub
        'build array
        i = i + 1
        ReDim Preserve Data(1 To i)
        'data values to array
        Data(i) = Item.Value
    Next
    
    
    'find the next blank row in range
    DataRow = wsAdhocHours.Cells(wsAdhocHours.Rows.Count, "A").End(xlUp).Row + 1
    'output array to range
    wsAdhocHours.Cells(DataRow, 1).Resize(1, CellCount).Value = Data
    
    'clear input - optional
    'DataEntryRange.Cells.SpecialCells(xlCellTypeConstants).ClearContents
    
    
    
myerror:
    With Application
        .EnableEvents = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
    Set wsAdhocHours = Nothing
    'tell user what happened
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error" Else MsgBox "Request Added", 48, "Request Added"
End Sub

3 - Press Alt+Q to return to Excel

4 - From Developer tab exit design mode

Enter data in your input fields & when done, press submit button which should add record to your database. Any fields not completed a message box will inform user all fields need to be completed.

I have given option in code to clear the entry after submission if required.

Hope helpful

Dave
 
Upvote 0
Cheers, I've completed the instructions as above - got an error at first with DataEntryRange(CellCount)... managed to sort that - it worked and I got the confirmation that the request had been added as expected

I then went into the adhoc hours spreadsheet and rather than it being the values it'd transferred - it's copied the value of cell H6 to each cell in the new row of adhoc hours... any ideas?
 
Upvote 0
delete the line

Set DataEntry = DataEntryRange(CellCount)

Dave
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top