UpToTheGills
New Member
- Joined
- Aug 26, 2022
- Messages
- 38
- Office Version
- 2019
- Platform
- MacOS
… because macOS excel ¯\_(ツ)_/¯
(Using Excel 2019 v.16.65 on macOS so no built-in Forms creation functionality.)
From googling, the alternative solution if you're on macOS is to use VBA code and I found this helpful webpage with example code so I'm attempting to adapt it for my own use case but I'm not familiar with VBA.
My use case is for a trade journal spreadsheet.
To use a data entry form on a worksheet (called Data Input
) to insert trades into the trade journal worksheet.
The data entry cells are the ones coloured green:
I will use XL2BB to get a section of the journal worksheet posted up shortly.
Looking forward to learning something new and gratefully for any and all help received. Thanks in advance.
(Using Excel 2019 v.16.65 on macOS so no built-in Forms creation functionality.)
From googling, the alternative solution if you're on macOS is to use VBA code and I found this helpful webpage with example code so I'm attempting to adapt it for my own use case but I'm not familiar with VBA.
My use case is for a trade journal spreadsheet.
To use a data entry form on a worksheet (called Data Input
) to insert trades into the trade journal worksheet.The data entry cells are the ones coloured green:
VBA Code:
myCopy = "F5,F6,F8,F9,F11,F12,F13,F14,F16,F17,F18,F19,F20"- F5 and F6 contain the formula
=TODAY()to save input however if overwritten with a date other than the current day, I want the code to check cells F5 and F6 and reinsert the=TODAY()if necessary. - The worksheet where the data is to be inserted is arranged with the latest data at the top rather than at the bottom whereby additional lines are inserted when needed. I need the code to check that there are free rows to insert the data, if not, to stop and show a warning OR ideally, to run one of the macros I have to insert new rows.
- One additional point is that although the date and time are separate on the Data Input worksheet, in the journal worksheet they are combined in a single cell.
VBA Code:
Sub UpdateTradeJournal()
'some of these DIMs may either not be needed or incorrect or perhaps need additional ones?
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCopy As String
Dim myCell As Range
'cells to copy from Input sheet
myCopy = "F5,F6,F8,F9,F11,F12,F13,F14,F16,F17,F18,F19,F20"
Set inputWks = Worksheets("Data Input")
Set historyWks = Worksheets("IC Live 4004xxxx")
'haven't tried to amend any of the below code as yet
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
Set myRng = .Range(myCopy)
If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End SubI will use XL2BB to get a section of the journal worksheet posted up shortly.
Looking forward to learning something new and gratefully for any and all help received. Thanks in advance.
