Deleting rows using userForm

[da_phat]

hye,
im a beginner in VBA.I need to create a form where i can delete a rows based on the user input.Basically the form will have 1 textbox and 1 combobox where, the combobox will consist all the 12 month. Secondly the textbox,where the text box will receive an input from a user.Once a button 'delete' is pressed,it will function where rows in which worksheet selected will be deleted accordingly.

Can anyone help me..pls

[geekgirlau]

Can you post a sanitised version of your workbook?

[da_phat]

erk,
can u tell me how can i attach my files

thx

[geekgirlau]

If you click on Go Advanced you will see a button below the reply area that says Manage attachments. This will let you browse to your workbook and upload it into your post.

[Bob Phillips]

hye,
im a beginner in VBA.I need to create a form where i can delete a rows based on the user input.Basically the form will have 1 textbox and 1 combobox where, the combobox will consist all the 12 month. Secondly the textbox,where the text box will receive an input from a user.Once a button 'delete' is pressed,it will function where rows in which worksheet selected will be deleted accordingly.

Can anyone help me..pls

So the combobox selecets the month/sheet?

Presumably the text bow will have the rows to be deleted, like 17:21 for instance?

[da_phat]

sorry for the late reply.Here the files.

1)For function extract, you can key-in year range 2002-2004
2)For second fucntion(delete).I would like,if i select the worksheet,then i can delete a row with an input of ServiceID in the text box.
3)For the 3rd function(add).I would like to add a data on the selected sheet,before the "Totals" rows.

The macro is inside file named car.xls . The function shouls be done on file 2002byservices.xls. I cant attach the .mdb file because its too big

Thx guys.hope you can help me on this one.

[mdmackillop]

Hi da-phat
Welcome to VBAX.
This code assumes that the data and userform are in the same workbook. The Service ID is to be entered into the textbox. It will delete only one record on the target sheet. Can there be more than one?



Option Explicit
Private Sub UserForm_Initialize()
ComboBox1.Value = "Select"
With ComboBox1
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "December"
End With
End Sub
Private Sub CommandButton1_Click()
Dim shtSeason As Worksheet
Dim c As Range
Set shtSeason = Sheets(ComboBox1.Text)
Set c = shtSeason.Columns(1).Find(TextBox1.Text)
c.Resize(, 6).Delete Shift:=xlUp
End Sub

[da_phat]

the code seems doesnt to be working.the combo box is working but the delete function is not working.

[da_phat]

sory,i've just checked the code n modified it a bit. Just need to change from "textBox1" to "textbox2". But can anyone tell me how to add data between a line. I mean before the "Totals" row. The file i've uploaded

[mdmackillop]

Are you looking to add it using one of your userforms?

[da_phat]

yes i am.The form is in the excel macro file. The function i need is when i select a specific worksheet(month worksheet), i can add the data just before the "Totals" row. The data must be added according to the header such as "ServiceID",ServiceDescriptio" etc..

[mdmackillop]

I've added Net Income as a calculated field. You should consider entering Discount and GST as percentages and using the code to enter either formuae or calculated results.


Option Explicit
Private Sub CommandButton1_Click()
Dim LastRow As Object, Response As Long
Dim shtSeason As Worksheet, intCell As Range

Set shtSeason = Sheets(ComboBox1.Text)

Set intCell = Cells(Rows.Count, 1).End(xlUp)
With intCell
.Resize(, 8).Insert
.Offset(-1, 0) = Me.TextBox1.Text
.Offset(-1, 1) = Me.TextBox2.Text
.Offset(-1, 2) = Me.TextBox3.Text
.Offset(-1, 3) = Me.TextBox4.Text
.Offset(-1, 4).FormulaR1C1 = "=RC[-2]-RC[-1]"
.Offset(-1, 5) = Me.TextBox6.Text
.Offset(-1, 6).FormulaR1C1 = "=RC[-2]+RC[-1]"
Range(.Offset(, 2), .Offset(, 7)).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
End With
MsgBox "One record written to Sheet1"
Response = MsgBox("Do you want to enter another record?", _
vbYesNo)
If Response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End Sub

[da_phat]

thx MdMacKillop.The code pretty much help me.

[da_phat]

erk, 1 more thing.i try to add validation to my text box on "add" function.But everytime i click yes for "adding another new data" the msgbox validation i created keep poping up.Here is the code maybe you can rectify it a bit. Private Sub TextBox1_Change()
With Me.TextBox1
If Not IsNumeric(.Text) Or Len(.Text) >= 5 Then
MsgBox "Only Up to 4 digits of input. " _
& "Enter Service Id with 3 or 4 digits only."
'Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub

Thx

[mdmackillop]

I've never sorted out resetting the original textbox, but this should check for a valid entry

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox1
If Not .Text > 99 And .Text < 10000 Then
MsgBox "Only Up to 4 digits of input. " _
& "Enter Service Id with 3 or 4 digits only."
End If
End With
End Sub

[da_phat]

hye mdmackillop.thx again.I have few more question if u dont mind which is:
1)every time i extract my data from access, a circular reference window keep poping up on the workbook.some a warning message also popin up.Can u check my code where have i gone wrong.
2)How can i make when i want to add data,if i entered a same "service id" number,a msg box will pop up saying that the service id already exist and i cant enter the same service id again.

Thx

[mdmackillop]

Can you repost your code with your modifications to date?

[da_phat]

hereby i enclose the file...

[da_phat]

hurm..its ok,i finally solve the circular reference problem.Just want to know how can i make validation if the data already exist in the worksheet.