Atlantis764
New Member
- Joined
- Jan 10, 2022
- Messages
- 21
- Office Version
- 2019
- Platform
- Windows
Hi all,
I have a User Form and when I enter a new record all the data are transferred to another sheet (Database).
All the records are filled on column E but I want to be filled on the corresponding column (based on column B -Year and column D - Month).
I filled with yellow the cells where I need the values to be imputed.
The VBA code that I am using is this:
Thanks for all your help!
I have a User Form and when I enter a new record all the data are transferred to another sheet (Database).
All the records are filled on column E but I want to be filled on the corresponding column (based on column B -Year and column D - Month).
I filled with yellow the cells where I need the values to be imputed.
Book1.xlsm | |||
---|---|---|---|
J | |||
10 | |||
Database |
The VBA code that I am using is this:
VBA Code:
Option Explicit
Sub Reset()
Dim iRow As Long
iRow = [Counta(Database!A:A)]
With UserForm1
.TxtHours.Value = ""
.CmbYear.Clear
.CmbName.Clear
.CmbMonth.Clear
.CmbYear.AddItem "2022"
.CmbYear.AddItem "2023"
.CmbYear.AddItem "2024"
.CmbName.AddItem "aaa"
.CmbName.AddItem "bbb"
.CmbName.AddItem "ccc"
.CmbName.AddItem "ddd"
.CmbName.AddItem "eee"
.CmbMonth.AddItem "January"
.CmbMonth.AddItem "February"
.CmbMonth.AddItem "March"
.CmbMonth.AddItem "April"
.CmbMonth.AddItem "May"
.CmbMonth.AddItem "June"
.CmbMonth.AddItem "July"
.CmbMonth.AddItem "August"
.CmbMonth.AddItem "September"
.CmbMonth.AddItem "October"
.CmbMonth.AddItem "November"
.CmbMonth.AddItem "December"
.LstDatabase.ColumnCount = 7
.LstDatabase.ColumnHeads = True
.LstDatabase.ColumnWidths = "30,50,60,60,30,30,30"
If iRow > 1 Then
.LstDatabase.RowSource = "Database!A2:AB" & iRow
Else
.LstDatabase.RowSource = "Database!A2:AB2"
End If
End With
End Sub
Sub Submit()
Dim sh As Worksheet
Dim iRow As Long
Set sh = ThisWorkbook.Sheets("Database")
iRow = [Counta(Database!A:A)] + 1
With sh
.Cells(iRow, 1) = iRow - 1
.Cells(iRow, 2) = UserForm1.CmbYear.Value
.Cells(iRow, 3) = UserForm1.CmbName.Value
.Cells(iRow, 4) = UserForm1.CmbMonth.Value
.Cells(iRow, 5) = UserForm1.TxtHours.Value
.Cells(iRow, 29) = Application.UserName
End With
End Sub
Sub Show_Form()
UserForm1.Show
End Sub
Thanks for all your help!
Attachments
Last edited by a moderator: