do i really have to manually extend the sheet every data entry

D

dgwan

New Member
Joined
May 4, 2022
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
Code: 
Private Sub CommandButton1_Click()
Me.AreaBox = ""
Me.MonthBox = ""
Me.DayBox = ""
Me.YearBox = ""
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
Me.TextBox5 = ""
Me.TextBox6 = ""
End Sub

Private Sub CommandButton2_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DATA")
Dim last_Row As Long
last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'Validations-------------------------------------------
If Me.AreaBox.Value = "" Then
MsgBox "Please select the Area.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.MonthBox.Value = "" Then
MsgBox "Please select the Month.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.DayBox.Value = "" Then
MsgBox "Please select the Day.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.YearBox.Value = "" Then
MsgBox "Please select the Year.", vbCritical
Exit Sub
End If
'------------------------------------------------------
sh.Range("A" & last_Row + 1).Value = "=ROW() -1"
sh.Range("B" & last_Row + 1).Value = Me.MonthBox.Value
sh.Range("C" & last_Row + 1).Value = Me.DayBox.Value
sh.Range("D" & last_Row + 1).Value = Me.YearBox.Value
sh.Range("E" & last_Row + 1).Value = Me.AreaBox.Value
sh.Range("F" & last_Row + 1).Value = Me.TextBox1.Value
sh.Range("G" & last_Row + 1).Value = Me.TextBox2.Value
sh.Range("H" & last_Row + 1).Value = Me.TextBox3.Value
sh.Range("I" & last_Row + 1).Value = Me.TextBox4.Value
sh.Range("J" & last_Row + 1).Value = Me.TextBox5.Value
'------------------------------------------------------
Me.MonthBox.Value = ""
Me.DayBox.Value = ""
Me.YearBox.Value = ""
Me.AreaBox.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
'------------------------------------------------------
Call Refresh_Data

End Sub

Private Sub CommandButton3_Click()

If Me.TextBox6.Value = "" Then
MsgBox "Select the record to update."
Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DATA")
Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox6.Value), sh.Range("A:A"), 0)
'Validations-------------------------------------------
If Me.AreaBox.Value = "" Then
MsgBox "Please select the Area.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.MonthBox.Value = "" Then
MsgBox "Please select the Month.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.DayBox.Value = "" Then
MsgBox "Please select the Day.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.YearBox.Value = "" Then
MsgBox "Please select the Year.", vbCritical
Exit Sub
End If
'------------------------------------------------------
sh.Range("B" & Selected_Row).Value = Me.MonthBox.Value
sh.Range("C" & Selected_Row).Value = Me.DayBox.Value
sh.Range("D" & Selected_Row).Value = Me.YearBox.Value
sh.Range("E" & Selected_Row).Value = Me.AreaBox.Value
sh.Range("F" & Selected_Row).Value = Me.TextBox1.Value
sh.Range("G" & Selected_Row).Value = Me.TextBox2.Value
sh.Range("H" & Selected_Row).Value = Me.TextBox3.Value
sh.Range("I" & Selected_Row).Value = Me.TextBox4.Value
sh.Range("J" & Selected_Row).Value = Me.TextBox5.Value
'------------------------------------------------------
Me.MonthBox.Value = ""
Me.DayBox.Value = ""
Me.YearBox.Value = ""
Me.AreaBox.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
'------------------------------------------------------
Call Refresh_Data

End Sub

Private Sub CommandButton4_Click()
If Me.TextBox6.Value = "" Then
MsgBox "Select the record to delete."
Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DATA")
Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox6.Value), sh.Range("A:A"), 0)
'------------------------------------------------------
sh.Range("A" & Selected_Row).EntireRow.Delete
'------------------------------------------------------
Me.MonthBox.Value = ""
Me.DayBox.Value = ""
Me.YearBox.Value = ""
Me.AreaBox.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""

Call Refresh_Data

End Sub

Private Sub CommandButton5_Click()
ThisWorkbook.Save
MsgBox "Data Saved!"
End Sub


Private Sub Frame1_Click()

End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.MonthBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.DayBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.YearBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.AreaBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)
Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 7)
Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 8)
Me.TextBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 9)

End Sub

Private Sub TextBox7_Change()

End Sub

Private Sub MonthBox_Change()

End Sub

Private Sub UserForm_Activate()
With Me.AreaBox
        .Clear
        .AddItem "7th WEST"
        .AddItem "6th WEST"
        .AddItem "6th ICU"
        .AddItem "5th WEST"
        .AddItem "5th EAST"
        .AddItem "4th ICU"
        .AddItem "3rd WEST"
        .AddItem "3rd EAST"
End With
With Me.MonthBox
        .Clear
        .AddItem "January"
        .AddItem "February"
        .AddItem "March"
        .AddItem "April"
        .AddItem "May"
        .AddItem "June"
        .AddItem "July"
        .AddItem "August"
        .AddItem "September"
        .AddItem "October"
        .AddItem "November"
        .AddItem "December"
End With
With Me.DayBox
        .Clear
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
        .AddItem "5"
        .AddItem "6"
        .AddItem "7"
        .AddItem "8"
        .AddItem "9"
        .AddItem "10"
        .AddItem "11"
        .AddItem "12"
        .AddItem "13"
        .AddItem "14"
        .AddItem "15"
        .AddItem "16"
        .AddItem "17"
        .AddItem "18"
        .AddItem "19"
        .AddItem "20"
        .AddItem "21"
        .AddItem "22"
        .AddItem "23"
        .AddItem "24"
        .AddItem "25"
        .AddItem "26"
        .AddItem "27"
        .AddItem "28"
        .AddItem "29"
        .AddItem "30"
        .AddItem "31"
End With
With Me.YearBox
        .Clear
        .AddItem "2022"
        .AddItem "2021"
        .AddItem "2020"
        .AddItem "2019"
        .AddItem "2018"
        .AddItem "2017"
        .AddItem "2016"
        .AddItem "2015"
End With

Call Refresh_Data
End Sub

Sub Refresh_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DATA")
Dim last_Row As Long
last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

With Me.ListBox1
        .ColumnHeads = True
        .ColumnCount = 10
        .ColumnWidths = "40, 50, 30, 30, 55, 60, 65, 65, 40, 40"
        .TextAlign = fmTextAlignCenter
        
        If last_Row = 1 Then
        .RowSource = "DATA!A2:J2"
        Else
        .RowSource = "DATA!A2:J" & last_Row
        End If
        
End With
End Sub
 
Sort by date Sort by votes
@Fluff Well, he didn't say much and left to our "divination skills" guessing the area of the problem; I am quite weak in that area... Ha ha... (not only in that area, he he)
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try the code I suggested. ;)
 
Upvote 0
Did you also add the UserForm_Initialize code (message #8)

Messages from me and from Fluff are interlaced, put them in the right order
 
Upvote 0
That is not the problem, as per post#10
Click to expand...
I still think the UserForm_Initialize code would be necessary IF the form is loaded and unloaded, that I don't know if it happens in the user process
 
Upvote 0
Fluff said:
From what the OP has said I don't think it's a problem with finding the last row, but more the data isn't going into the table.
Click to expand...
yeah everything goes in the last row but the first entry becomes the Total Row even if i turned it off in Table Style Option.. that's why it separates the second entry in the table.. the first entry becomes the total row of the table..
 
Upvote 0
Have you tried the code I suggested?
 
Upvote 0
Fluff said:
From what the OP has said I don't think it's a problem with finding the last row, but more the data isn't going into the table.
Click to expand...

from a quick glance Couple observations if I may

- you have duplicate declarations in same scope
- try disconnecting the RowSource Before posting data to table & see if this resolves

Dave

Rich (BB code): 
Private Sub CommandButton2_Click()
Dim nxtrw As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim nxtrw As ListRow
'Validations-------------------------------------------
If Me.AreaBox.Value = "" Then
MsgBox "Please select the Area.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.MonthBox.Value = "" Then
MsgBox "Please select the Month.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.DayBox.Value = "" Then
MsgBox "Please select the Day.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.YearBox.Value = "" Then
MsgBox "Please select the Year.", vbCritical
Exit Sub
End If
'------------------------------------------------------

Me.ListBox1.RowSource = ""

Set nxtrw = sh.ListObjects("NSOTable").ListRows.Add
nxtrw.Range(1).Value = "=ROW() -1"
nxtrw.Range(2).Value = Me.MonthBox.Value
nxtrw.Range(3).Value = Me.DayBox.Value
nxtrw.Range(4).Value = Me.YearBox.Value
nxtrw.Range(5).Value = Me.AreaBox.Value
nxtrw.Range(6).Value = Me.TextBox1.Value
nxtrw.Range(7).Value = Me.TextBox2.Value
nxtrw.Range(8).Value = Me.TextBox3.Value
nxtrw.Range(9).Value = Me.TextBox4.Value
nxtrw.Range(10).Value = Me.TextBox5.Value
'------------------------------------------------------
Me.MonthBox.Value = ""
Me.DayBox.Value = ""
Me.YearBox.Value = ""
Me.AreaBox.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
'------------------------------------------------------
Call Refresh_Data

End Sub
 
Upvote 0
Solution
dmt32 said:
from a quick glance Couple observations if I may

- you have duplicate declarations in same scope
- try disconnecting the RowSource Before posting data to table & see if this resolves

Dave

Rich (BB code): 
Private Sub CommandButton2_Click()
Dim nxtrw As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim nxtrw As ListRow
'Validations-------------------------------------------
If Me.AreaBox.Value = "" Then
MsgBox "Please select the Area.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.MonthBox.Value = "" Then
MsgBox "Please select the Month.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.DayBox.Value = "" Then
MsgBox "Please select the Day.", vbCritical
Exit Sub
End If
'------------------------------------------------------
If Me.YearBox.Value = "" Then
MsgBox "Please select the Year.", vbCritical
Exit Sub
End If
'------------------------------------------------------

Me.ListBox1.RowSource = ""

Set nxtrw = sh.ListObjects("NSOTable").ListRows.Add
nxtrw.Range(1).Value = "=ROW() -1"
nxtrw.Range(2).Value = Me.MonthBox.Value
nxtrw.Range(3).Value = Me.DayBox.Value
nxtrw.Range(4).Value = Me.YearBox.Value
nxtrw.Range(5).Value = Me.AreaBox.Value
nxtrw.Range(6).Value = Me.TextBox1.Value
nxtrw.Range(7).Value = Me.TextBox2.Value
nxtrw.Range(8).Value = Me.TextBox3.Value
nxtrw.Range(9).Value = Me.TextBox4.Value
nxtrw.Range(10).Value = Me.TextBox5.Value
'------------------------------------------------------
Me.MonthBox.Value = ""
Me.DayBox.Value = ""
Me.YearBox.Value = ""
Me.AreaBox.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
'------------------------------------------------------
Call Refresh_Data

End Sub
Click to expand...
i used this code and i had an error where theres a duplicate declaration so i changed the variable into "Dim n As Worksheet" and it finally worked! i did all the notes u added. I only had limited time to create this so i appreciate the big help! Thank you sm!
 
Upvote 0
You must log in or register to reply here.

Similar threads

SemperFi
  • Solved
VBA Form TextBox needs formatting
Replies
1
Views
335
SemperFi
SemperFi
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
320
pure vito
P
P
  • Question
Putting data into ListBox1 from UserForm6 to Userform1 listbox
Replies
5
Views
275
dmt32
D
X
  • Solved
Displayed numeric date instead of the entered format date
Replies
6
Views
335
hicksi
hicksi
X
  • Solved
It didn't change the forecolor of the textbox
Replies
4
Views
339
Fluff
Fluff

Forum statistics

Threads
1,214,830
Messages
6,121,835
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top