VBA code for a User form

[Atlantis764]

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.

Book1.xlsm
	J
10
Database

The VBA code that I am using is this:

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!

 

[Saurabhj]

Hi,

Change Submit code as below:

Sub Submit()
    Dim sh As Worksheet
    Dim iRow As Long, colno As Integer, iCol As Long
    Set sh = ThisWorkbook.Sheets("Database")
    iRow = [Counta(Database!A:A)] + 1
    iCol = Sheets("Database").Cells(1, Columns.Count).End(xlToLeft).Column - 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
        For colno = 5 To iCol
            If UserForm1.CmbMonth.Value = Format(.Cells(1, colno), "MMMM") And _
            UserForm1.CmbYear.Value = Format(.Cells(1, colno), "YYYY") Then
                .Cells(iRow, colno) = UserForm1.txtHours.Value
            End If
        Next
        .Cells(iRow, iCol+1) = Application.UserName
    End With
End Sub

 

[Atlantis764]

Hi,

Change Submit code as below:

Sub Submit()
    Dim sh As Worksheet
    Dim iRow As Long, colno As Integer, iCol As Long
    Set sh = ThisWorkbook.Sheets("Database")
    iRow = [Counta(Database!A:A)] + 1
    iCol = Sheets("Database").Cells(1, Columns.Count).End(xlToLeft).Column - 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
        For colno = 5 To iCol
            If UserForm1.CmbMonth.Value = Format(.Cells(1, colno), "MMMM") And _
            UserForm1.CmbYear.Value = Format(.Cells(1, colno), "YYYY") Then
                .Cells(iRow, colno) = UserForm1.txtHours.Value
            End If
        Next
        .Cells(iRow, iCol+1) = Application.UserName
    End With
End Sub

Thank you very much!!!
The code works great!

 

[Saurabhj]

Great. Thanks for the feedback.

 

[Atlantis764]

Great. Thanks for the feedback.

I am trying to redesign the User Form now and for sure I will need your help in the future.
Many thanks again!