VBA code for excel form

[phillip87]

I have made a data form which I would like to always show on "sheet1" and add new lines of data to "sheet2" horizontally so I can then work with the data without having people mess with it however I keep getting debug errors.

I have tried looking online however everything i find is too complicated and i am too new to VBA to debug them.

can someone help me out with the VBA code on this, new to the VBA so will not bother attaching what I have, just need something simple to work the below.

my form has the following.

date (text box)
department (combo box)
file numbers(text box)
box numbers(text box)
add(command box)
new(command box)

Thanks.​

 

[phillip87]

Re: help with a simple VBA code for excel form

https://drive.google.com/open?id=0B5-WR8lT5JrCWlY3cWxOTnk1RFc1bENndnFSRERWUkdBZVZn

 

[phillip87]

Re: help with a simple VBA code for excel form

https://drive.google.com/open?id=0B5-WR8lT5JrCWlY3cWxOTnk1RFc1bENndnFSRERWUkdBZVZn

couldn't drop box it but have shared via google, shows how the form is input to the sheet.

i put the form button on another TAB but it doesn't seem to be showing up.

 

[phillip87]

Re: help with a simple VBA code for excel form

OK so the formatting now seems to be working, not sure why it wasn't before.

thanks for your help though.

 

[dmt32]

Re: help with a simple VBA code for excel form

OK so the formatting now seems to be working, not sure why it wasn't before.

thanks for your help though.

Glad resolved - many thanks for feedback

Dave

 

[phillip87]

Re: help with a simple VBA code for excel form

Hey Dave, is there anyway to tweak the above so the button saves to the "KPI DATA" sheet but in a different workbook??

my boss keeps changing the reports against the raw data which mess with the dashboard i have attached to this so i would like to separate the two.

 

[dmt32]

Re: help with a simple VBA code for excel form

Hey Dave, is there anyway to tweak the above so the button saves to the "KPI DATA" sheet but in a different workbook??

my boss keeps changing the reports against the raw data which mess with the dashboard i have attached to this so i would like to separate the two.

untested but try this update

Private Sub CommandButton1_Click()
    Dim wsKPI As Worksheet
    Dim wbKPI As Workbook
    Dim FolderName As String, wbName As String
    Dim wsName As String
    Dim lr As Long
    Dim i As Integer
    Dim arr(1 To 20) As Variant
    
'*******************************************************************************************************
'*********************************************SETTINGS**************************************************


'********************CHANGE THE FOLDER NAME, WORKBOOK & WORKSHEET NAMES AS REQUIRED*********************
    
    FolderName = "C:\Users\phillip87\Documents\KPI Data"
    
    wbName = "KPI Data.xlsx"
    
    wsName = "PENROSE KPI DATA"
    
'*******************************************************************************************************
    
    Application.ScreenUpdating = False
    On Error Resume Next
'check if workbook open
    Set wbKPI = Workbooks(wbName)
    
    On Error GoTo myerror
    If wbKPI Is Nothing Then
'open workbook
        Set wbKPI = Workbooks.Open(FolderName & "\" & wbName, False, False)
    End If
    
    Set wsKPI = wbKPI.Worksheets(wsName)
    
    For i = 1 To UBound(arr)
        With Me.Controls(IIf(i = 2, "Combobox", "TextBox") & IIf(i = 2, 1, IIf(i > 2, i - 1, i)))
            If IsDate(.Value) Then
                arr(i) = DateValue(.Value)
            ElseIf IsNumeric(.Value) Then
                arr(i) = Val(.Value)
            Else
                arr(i) = (.Value)
            End If
'clear textboxes
            .Value = ""
        End With
    Next i
        
        With wsKPI
            lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1
'add record to worksheet
            .Cells(lr, 1).Resize(, UBound(arr)).Value = arr
        End With
'close wb & save
        wbKPI.Close True
        Set wbKPI = Nothing
        
myerror:
        If Not wbKPI Is Nothing Then wbKPI.Close False
        Application.ScreenUpdating = True
        If Err <> 0 Then
'report errors
            MsgBox (Error(Err)), 48, "Error"
        Else
            MsgBox "Record Submitted", 48, "Record Submitted"
        End If
End Sub

Change values in settings section as required


Dave

 

[phillip87]

Re: help with a simple VBA code for excel form

i have it working but only if the other workbook is open.

if the other workbook is closed it sends an error and shows it looking for the WB inside the WB inside the folder rather than just the WB inside the folder.

 

[dmt32]

Re: help with a simple VBA code for excel form

i have it working but only if the other workbook is open.

if the other workbook is closed it sends an error and shows it looking for the WB inside the WB inside the folder rather than just the WB inside the folder.

hi, should work OK

have you updated FolderName variable correctly?

FolderName = "C:\Users\phillip87\Documents\KPI Data"

Note there is NO back slash \ at the end.

Dave

 

[phillip87]

Re: help with a simple VBA code for excel form

FolderName = "P:\Penrose work\KPI Data\Dashboard Data and reports"

it works if the data WB is open but if only the dashboard is open then it cannot find it.

it says cannot find in "P:\Penrose work\KPI Data\Dashboard Data and reports\Dashboard Data and reports"

 

[phillip87]

Re: help with a simple VBA code for excel form

Private Sub CommandButton1_Click()
Dim wsKPI As Worksheet
Dim wbKPI As Workbook
Dim FolderName As String, wbName As String
Dim wsName As String
Dim lr As Long
Dim i As Integer
Dim arr(1 To 20) As Variant

'*******************************************************************************************************
'*********************************************SETTINGS**************************************************




'********************CHANGE THE FOLDER NAME, WORKBOOK & WORKSHEET NAMES AS REQUIRED*********************

FolderName = "P:\Penrose work\KPI Data\Dashboard Data and reports"

wbName = "Dashboard Data and reports.xlsx"

wsName = "PENROSE KPI DATA"

'*******************************************************************************************************