Standard entry from another worksheet

[bartmanekul]

I just need a pointer for this one.

Tried googling, but think I'm putting the wrong terms in.


I have a template spreadsheet, which people use for scoring.

This data needs to go into a table, and although I've made it as easy as possible - set it up so they only have to copy and paste a single line into the data table, I'd still much rather not have them in the table in the first place.

So first:

Is it possible to include a button for them to press on this template sheet which will add the results to a new row in a data table in another spreadsheet?

If so, how do I do this? I've never touched VBA, though if there's existing code I can likely modify to my needs.

Again, if possible, is there anything that can be done to stop someone from adding an entry twice (i.e. they press the button again)?

Thanks in advance.

 

[My Aswer Is This]

Do you mean in another "Sheet" or in another "Workbook"?

You used the term "Spreadsheet" which actually refers to another "Workbook"

You need to give us details on exact sheet names and if your referring to another Workbook then we need the name of both workbooks and the exact sheet names and the range to past into.

What your wanting is possible but we always need exact details.

 

[bartmanekul]

Do you mean in another "Sheet" or in another "Workbook"?

You used the term "Spreadsheet" which actually refers to another "Workbook"

You need to give us details on exact sheet names and if your referring to another Workbook then we need the name of both workbooks and the exact sheet names and the range to past into.

What your wanting is possible but we always need exact details.

Sorry, poor terminology.

A workbook template is used by many. This puts all the relevant data into a single row, which follows the same columns as where I want it to go.


The data table is on a separate workbook. This is called (imaginatively) 'Datatable2'.

I'm not looking for someone to write all the code for me, I can replace anything like column names, sheetnames etc.

 

[My Aswer Is This]

I will keep watching this thread but will need someone else here at Mr.Excel to Help you. I'm not good at just giving general advise. I thought you were wanting a script written.

Trying to keep users from entering data twice and pasting to ranges they cannot see can get complicated.

 

[bartmanekul]

Ah, well that's a nice to have, but not a requirement.

 

[dmt32]

Hi,
would I be correct in assuming that your Template & Database workbooks each only contain on worksheet?
Also, your users all have access to your database workbook via your corporate network?

Dave

 

[bartmanekul]

The data table has more than one worksheet, but the table itself is only on a single sheet. If needs be the workbook can be made a single sheet.

The template workbook is only a single sheet.

The people using the template do have access to the location of the workbook with the table. However, it's password protected for read only access (again, if needs be this can be removed).

 

[dmt32]

The data table has more than one worksheet, but the table itself is only on a single sheet. If needs be the workbook can be made a single sheet.

The template workbook is only a single sheet.

The people using the template do have access to the location of the workbook with the table. However, it's password protected for read only access (again, if needs be this can be removed).

Hi,
With database workbook, ensure that the table is the FIRST sheet & that it can be opened read / write. Password to open is not an issue.

Template workbook - what are the input cell addresses?


Let me know when can I will post some code for you to try - may be a delay in my responding as I am on granddad duties today.

Dave

 

[bartmanekul]

Hi,
With database workbook, ensure that the table is the FIRST sheet & that it can be opened read / write. Password to open is not an issue.

Template workbook - what are the input cell addresses?


Let me know when can I will post some code for you to try - may be a delay in my responding as I am on granddad duties today.

Dave

Seriously, don't worry about time, I appreciate you doing it regardless.

I've put them all in a row. So from the template, it's B118 to BM118, all in correct order. Though there will be blank/empty cells inbetween as there is not always data in every single cell. Let me know if that's a problem.

 

[dmt32]

Hi,
Following code is updated from code I have created for others here on forum with similar requirement:

1 - Place BOTH codes in a STANDARD module of your template workbook.

Sub SaveTemplateData()


    Dim DatabaseName As String, DatabasePassword As String
    Dim TemplateSheet As String
    Dim wbDatabase As Workbook, wbTemplate As Workbook
    Dim DatabaseRange As Range, DataEntryRange As Range, Item As Range
    Dim msg As Variant, Data() As Variant
    Dim i As Integer, InputCellCount As Integer
    Dim CompleteAllCells As Boolean
    
'**********************************************************************************************
'*******************************************SETTINGS*******************************************


'Database workbook open password - enter as required (case sensitive)
    DatabasePassword = ""


'Template Input Addresses
'cells can be both contiguous & non-contiguous
    TemplateInputAddress = "B118:BM118"


'data entry rules (Set True if ALL Cells must be completed)
    CompleteAllCells = False
    
'**********************************************************************************************


'Database Path / Name
    DatabaseName = Cells(Rows.Count, 1).Value


    If Len(DatabaseName) = 0 Then
        DatabaseName = BrowseFile
        If Len(DatabaseName) = 0 Then Exit Sub
        Cells(Rows.Count, 1).Value = DatabaseName
    End If


    On Error GoTo myerror
'check file / folder path valid
    If Not Dir(DatabaseName, vbDirectory) = vbNullString Then
        Application.ScreenUpdating = False
        Set wbTemplate = ThisWorkbook
        
'data entry range
        Set DataEntryRange = wbTemplate.Worksheets(1).Range(TemplateInputAddress)
           
'count No Input Cells
    InputCellCount = DataEntryRange.Cells.Count
    
    For Each Item In DataEntryRange.Cells
'check if required entry for all cells
        If CompleteAllCells And Len(Item.Value) = 0 Then
            MsgBox "Please Complete All Fields.", 16, "Entry Required"
            Item.Select
            Exit Sub
        End If
'build array
        i = i + 1
        ReDim Preserve Data(1 To i)
'data values to array
        Data(i) = Item.Value
    Next Item
    
'or if some blank cells allowed, check if any data entered
    If Not CompleteAllCells And Application.CountA(Range(TemplateInputAddress)) = 0 Then
        MsgBox "All Fields Are Empty.", 16, "Error"
    Exit Sub
    End If
    
'Open database
        Set wbDatabase = Workbooks.Open(DatabaseName, ReadOnly:=False, Password:=DatabasePassword)
        
'Next empty range in database
        With wbDatabase.Sheets(1)
            Set DatabaseRange = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
        End With


'output array to database range
        DatabaseRange.Resize(1, InputCellCount).Value = Data
'close & save
        wbDatabase.Close True
'clear form
        DataEntryRange.ClearContents
'report success
        msg = Array("Template Data Saved", "Data Saved")
    Else
'report problem
        msg = Array(DatabaseName & Chr(10) & "File Not Found", "Error")
    End If
    
myerror:
    Application.ScreenUpdating = True
    If Err > 0 Then
        If Not wbDatabase Is Nothing Then wbDatabase.Close False
        MsgBox (Error(Err)), 48, "Error"
    Else
        MsgBox msg(0), 48, msg(1)
    End If
'clean up
    Set wbDatabase = Nothing
    Set wbTemplate = Nothing
    Set DataEntryRange = Nothing
End Sub

Function BrowseFile() As String
    Dim FileName As Variant
    Dim sFilter As String


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


    FileName = Application.GetOpenFilename(sFilter, 1, "Select Database Workbook")
    
    BrowseFile = FileName


End Function

Place following code in Thisworkbook Code page:

 Private Sub Workbook_Open()    
  If ThisWorkbook.Path = "" Then Sheets(1).Cells(Rows.Count, 1).Value = ""
End Sub

Settings:

2 - ADD the workbook Open Password between the “” in the settings section. Remember, Passwords are case sensitive.

2.1 specify the cell addresses for the TemplateInputAddress variable as a string.
Cell addresses can be both contiguous & non-contiguous as required.

2.2 specify if ALL Cells must be completed by setting CompleteAllCells variable to True

3 – Add a Forms Button to the Template & Assign macro “SaveTemplateData” to it.

4 - Save the workbook as a Macro Enabled Workbook.

The Database workbook must be saved as a non macro workbook (xlsx) & be able to be opened Read / Write. The Database table must be the very first sheet in the workbook.

When you first press the button, you should be presented with the File Open dialog to locate the database file on your network – Locate the file & press ok – This will be saved to the template.

Try entering a record & press button, this should write the data to the database, save & close it. The entry will be cleared from the template.

If solution meets your requirement, lock the VBA project with password & then circulate workbook to your users.


Hope Helpful.

Dave.