Need help on Update Button on Userform

M

marty6

Hi,

Hi,

I have another "small" problem with updating information. I'm no
trying to attemp to update from "multipage two" with its "update
button on the userform. This "update" button only updates data o
sheet two. It updates specific items by number. If I input any ite
number for whoever, it changes the item for that person. This updat
button only updates current data being typed into the textboxes.
would also like to update the general data on sheet 1.

Here's an example of what I would like to do:

If I enter 1001.01 the select "update" on the userform, the person'
information is updated lets say from 1002.01. "1002.01" was th
person's other item at that time. As I select "update", I would lik
1001.01 on "Sheet 1" to increase by 1 and 1002.01 to decrease by 1.

Here's the coding so far:

Private Sub CommandButton1_Click()
IncDec TextBox4, True
IncDec TextBox5, True
IncDec TextBox6, True
IncDec TextBox7, True
IncDec TextBox8, True
IncDec TextBox9, True
IncDec TextBox10, True
Rem IncDec TextBox11, False
Rem IncDec TextBox12, False
Rem IncDec TextBox13, False
Rem IncDec TextBox14, False
Rem IncDec TextBox15, False
Rem IncDec TextBox16, False
Rem IncDec TextBox17, False
End Sub
Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long
With Worksheets("Sheet1")
On Error Resume Next
iItem = Evaluate("Match(" & textbox.Text & ",A1:H1, 0)")
If iItem > 0 Then
If Increment Then
Cells(2, iItem).Value = Cells(2, iItem).Value + 1
Else
Cells(2, iItem).Value = Cells(2, iItem).Value - 1
iItem = Evaluate("Match(" & textbox.Text & ",A3:H3, 0)")
If iItem > 0 Then
If Increment Then
Cells(4, iItem).Value = Cells(4, iItem).Value + 1
Else
Cells(4, iItem).Value = Cells(4, iItem).Value - 1
iItem = Evaluate("Match(" & textbox.Text & ",A5:H5, 0)")
If iItem > 0 Then
If Increment Then
Cells(6, iItem).Value = Cells(6, iItem).Value + 1
Else
Cells(6, iItem).Value = Cells(6, iItem).Value - 1
End If
End If
End If
End If
End If
End If
End With
End Sub


Private Sub CommandButton2_Click()
Dim lastRow As Object

Set lastRow = Sheet2.Range("a65536").End(xlUp)

lastRow.Offset(1, 0).Value = txtStudentID.Text
lastRow.Offset(1, 1).Value = txtLastName.Text
lastRow.Offset(1, 2).Value = txtFirstName.Text
lastRow.Offset(1, 3).Value = TextBox4.Text
lastRow.Offset(1, 4).Value = TextBox5.Text
lastRow.Offset(1, 5).Value = TextBox6.Text
lastRow.Offset(1, 6).Value = TextBox7.Text
lastRow.Offset(1, 7).Value = TextBox8.Text
lastRow.Offset(1, 8).Value = TextBox9.Text
lastRow.Offset(1, 9).Value = TextBox10.Text
Rem lastRow.Offset(1, 4).Value = TextBox11.Text
Rem lastRow.Offset(1, 5).Value = TextBox12.Text
Rem lastRow.Offset(1, 6).Value = TextBox13.Text
Rem lastRow.Offset(1, 7).Value = TextBox14.Text
Rem lastRow.Offset(1, 8).Value = TextBox15.Text
Rem lastRow.Offset(1, 9).Value = TextBox16.Text
Rem lastRow.Offset(1, 10).Value = TextBox17.Text


MsgBox "One record written to Sheet2"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
txtStudentID.Text = ""
txtLastName.Text = ""
txtFirstName.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""
TextBox17.Text = ""
txtStudentID.SetFocus

Else
Unload Me
End If


End Sub

Private Sub CommandButton3_Click()
End
End Sub

Private Sub CommandButton4_Click()
ActiveCell.Formula = TextBox18.Text
ActiveCell.Offset(0, 1).Formula = TextBox19.Text
ActiveCell.Offset(0, 2).Formula = TextBox20.Text
ActiveCell.Offset(0, 3).Formula = TextBox21.Text
ActiveCell.Offset(0, 4).Formula = TextBox22.Text
ActiveCell.Offset(0, 5).Formula = TextBox23.Text
ActiveCell.Offset(0, 6).Formula = TextBox24.Text
ActiveCell.Offset(0, 7).Formula = TextBox25.Text
ActiveCell.Offset(0, 8).Formula = TextBox26.Text
ActiveCell.Offset(0, 9).Formula = TextBox27.Text

MsgBox "Record Updated"
End Sub

Private Sub CommandButton5_Click()
Dim lastRow As Long
If ActiveSheet.Name = "Sheet1" Then
CommandButton5.Enabled = False
Else
If ActiveCell.Column <> 1 Then
Cells(ActiveCell.Row, 1).Select
End If
If ActiveCell.Row <> lastRow Then
ActiveCell.Offset(1, 0).Select
TextBox18.Text = ActiveCell.Value
TextBox19.Text = ActiveCell.Offset(0, 1).Value
TextBox20.Text = ActiveCell.Offset(0, 2).Value
TextBox21.Text = ActiveCell.Offset(0, 3).Value
TextBox22.Text = ActiveCell.Offset(0, 4).Value
TextBox23.Text = ActiveCell.Offset(0, 5).Value
TextBox24.Text = ActiveCell.Offset(0, 6).Value
TextBox25.Text = ActiveCell.Offset(0, 7).Value
TextBox26.Text = ActiveCell.Offset(0, 8).Value
TextBox27.Text = ActiveCell.Offset(0, 9).Value
End If
End If

End Sub


Private Sub CommandButton6_Click()
If ActiveSheet.Name = "Sheet1" Then
CommandButton6.Enabled = False
Else
If ActiveCell.Column <> 1 Then
Cells(ActiveCell.Row, 1).Select
End If
If ActiveCell.Row <> 1 Then
ActiveCell.Offset(-1, 0).Select
TextBox18.Text = ActiveCell.Value
TextBox19.Text = ActiveCell.Offset(0, 1).Value
TextBox20.Text = ActiveCell.Offset(0, 2).Value
TextBox21.Text = ActiveCell.Offset(0, 3).Value
TextBox22.Text = ActiveCell.Offset(0, 4).Value
TextBox23.Text = ActiveCell.Offset(0, 5).Value
TextBox24.Text = ActiveCell.Offset(0, 6).Value
TextBox25.Text = ActiveCell.Offset(0, 7).Value
TextBox26.Text = ActiveCell.Offset(0, 8).Value
TextBox27.Text = ActiveCell.Offset(0, 9).Value
End If
End If
End Sub

On Multipage1 of userform
Commandbutton1 represents "Entering Data" to sheet1
Commandbutton2 represents "Entering Data" to sheet2

On Multipage2 of userform
Commandbutton4 represents "Update Data" for Sheet 2 only
Commandbutton5 represents "Next" for sheet2 only
Commandbutton6 represents "Previous" for sheet2 only


Commandbutton3 represents "Closing the form"

Is there a way for Commandbutton4 to update data on sheets 1 and 2? If
anybody can help, look at the data under commandbutton1 and also
private sub incdec.

Any and all help is appreciated!

Thanks,

Marty6
 
M

marty6

Hi,

Would anybody know how this may work? Would I have to incorporat
vlookup in the scripting?

any help is appreciated....

marty
 
M

marty6

Hi,

I still appreciate anybody's response to this. I did try this thinkin
that it might work. But didn't. I keep getting an error:
Ambiguous Name detected: IncDec...... Under CommandButton7_Click()
did reuse IncDec thinking that it might work. Then I got this error.
I tried changing out the IncDec to for example IncAns and got thi
error: Argument not Optional.

I'm trying to use the second "update" button, which is button # 7 t
update sheet 1. Does anybody have any ideas on how to make this work?
Is there a way to delare both commandbuttons 1 and 7 to work with th
IncDec sub routine?

Here's the updated code:

Private Sub CommandButton1_Click()
IncAns TextBox4, True
IncDec TextBox5, True
IncDec TextBox6, True
IncDec TextBox7, True
IncDec TextBox8, True
IncDec TextBox9, True
IncDec TextBox10, True
Rem IncDec TextBox11, False
Rem IncDec TextBox12, False
Rem IncDec TextBox13, False
Rem IncDec TextBox14, False
Rem IncDec TextBox15, False
Rem IncDec TextBox16, False
Rem IncDec TextBox17, False
End Sub
Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long
With Worksheets("Sheet1")
On Error Resume Next
iItem = Evaluate("Match(" & textbox.Text & ",A1:H1, 0)")
If iItem > 0 Then
If Increment Then
Cells(2, iItem).Value = Cells(2, iItem).Value + 1
Else
Cells(2, iItem).Value = Cells(2, iItem).Value - 1
iItem = Evaluate("Match(" & textbox.Text & ",A3:H3, 0)")
If iItem > 0 Then
If Increment Then
Cells(4, iItem).Value = Cells(4, iItem).Value + 1
Else
Cells(4, iItem).Value = Cells(4, iItem).Value - 1
iItem = Evaluate("Match(" & textbox.Text & ",A5:H5, 0)")
If iItem > 0 Then
If Increment Then
Cells(6, iItem).Value = Cells(6, iItem).Value + 1
Else
Cells(6, iItem).Value = Cells(6, iItem).Value - 1
End If
End If
End If
End If
End If
End If
End With
End Sub


Private Sub CommandButton2_Click()
Dim lastRow As Object

Set lastRow = Sheet2.Range("a65536").End(xlUp)

lastRow.Offset(1, 0).Value = txtStudentID.Text
lastRow.Offset(1, 1).Value = txtLastName.Text
lastRow.Offset(1, 2).Value = txtFirstName.Text
lastRow.Offset(1, 3).Value = TextBox4.Text
lastRow.Offset(1, 4).Value = TextBox5.Text
lastRow.Offset(1, 5).Value = TextBox6.Text
lastRow.Offset(1, 6).Value = TextBox7.Text
lastRow.Offset(1, 7).Value = TextBox8.Text
lastRow.Offset(1, 8).Value = TextBox9.Text
lastRow.Offset(1, 9).Value = TextBox10.Text
Rem lastRow.Offset(1, 4).Value = TextBox11.Text
Rem lastRow.Offset(1, 5).Value = TextBox12.Text
Rem lastRow.Offset(1, 6).Value = TextBox13.Text
Rem lastRow.Offset(1, 7).Value = TextBox14.Text
Rem lastRow.Offset(1, 8).Value = TextBox15.Text
Rem lastRow.Offset(1, 9).Value = TextBox16.Text
Rem lastRow.Offset(1, 10).Value = TextBox17.Text


MsgBox "One record written to Sheet2"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
txtStudentID.Text = ""
txtLastName.Text = ""
txtFirstName.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""
TextBox17.Text = ""
txtStudentID.SetFocus

Else
Unload Me
End If


End Sub

Private Sub CommandButton3_Click()
End
End Sub

Private Sub CommandButton4_Click()
ActiveCell.Formula = TextBox18.Text
ActiveCell.Offset(0, 1).Formula = TextBox19.Text
ActiveCell.Offset(0, 2).Formula = TextBox20.Text
ActiveCell.Offset(0, 3).Formula = TextBox21.Text
ActiveCell.Offset(0, 4).Formula = TextBox22.Text
ActiveCell.Offset(0, 5).Formula = TextBox23.Text
ActiveCell.Offset(0, 6).Formula = TextBox24.Text
ActiveCell.Offset(0, 7).Formula = TextBox25.Text
ActiveCell.Offset(0, 8).Formula = TextBox26.Text
ActiveCell.Offset(0, 9).Formula = TextBox27.Text
MsgBox "Record Updated"
End Sub
Private Sub Commandbutton7_Click()
IncDec TextBox21 = True
IncDec TextBox22 = True
IncDec TextBox23 = True
IncDec TextBox24 = True
IncDec TextBox25 = True
IncDec TextBox26 = True
IncDec TextBox27 = True
End Sub
Private Sub IncAns(textbox As msforms.textbox, Increment As Boolean)

Dim iItem As Long
With Worksheets("Sheet1")
On Error Resume Next
iItem = Evaluate("Match(" & textbox.Text & ",A1:H1, 0)")
If iItem > 0 Then
If Increment Then
Cells(2, iItem).Value = Cells(2, iItem).Value + 1
Else
Cells(2, iItem).Value = Cells(2, iItem).Value - 1
iItem = Evaluate("Match(" & textbox.Text & ",A3:H3, 0)")
If iItem > 0 Then
If Increment Then
Cells(4, iItem).Value = Cells(4, iItem).Value + 1
Else
Cells(4, iItem).Value = Cells(4, iItem).Value - 1
iItem = Evaluate("Match(" & textbox.Text & ",A5:H5, 0)")
If iItem > 0 Then
If Increment Then
Cells(6, iItem).Value = Cells(6, iItem).Value + 1
Else
Cells(6, iItem).Value = Cells(6, iItem).Value - 1
End If
End If
End If
End If
End If
End If
End With
End Sub
Private Sub CommandButton5_Click()
Dim lastRow As Long
If ActiveSheet.Name = "Sheet1" Then
CommandButton5.Enabled = False
Else
If ActiveCell.Column <> 1 Then
Cells(ActiveCell.Row, 1).Select
End If
If ActiveCell.Row <> lastRow Then
ActiveCell.Offset(1, 0).Select
TextBox18.Text = ActiveCell.Value
TextBox19.Text = ActiveCell.Offset(0, 1).Value
TextBox20.Text = ActiveCell.Offset(0, 2).Value
TextBox21.Text = ActiveCell.Offset(0, 3).Value
TextBox22.Text = ActiveCell.Offset(0, 4).Value
TextBox23.Text = ActiveCell.Offset(0, 5).Value
TextBox24.Text = ActiveCell.Offset(0, 6).Value
TextBox25.Text = ActiveCell.Offset(0, 7).Value
TextBox26.Text = ActiveCell.Offset(0, 8).Value
TextBox27.Text = ActiveCell.Offset(0, 9).Value
End If
End If

End Sub


Private Sub CommandButton6_Click()
If ActiveSheet.Name = "Sheet1" Then
CommandButton6.Enabled = False
Else
If ActiveCell.Column <> 1 Then
Cells(ActiveCell.Row, 1).Select
End If
If ActiveCell.Row <> 1 Then
ActiveCell.Offset(-1, 0).Select
TextBox18.Text = ActiveCell.Value
TextBox19.Text = ActiveCell.Offset(0, 1).Value
TextBox20.Text = ActiveCell.Offset(0, 2).Value
TextBox21.Text = ActiveCell.Offset(0, 3).Value
TextBox22.Text = ActiveCell.Offset(0, 4).Value
TextBox23.Text = ActiveCell.Offset(0, 5).Value
TextBox24.Text = ActiveCell.Offset(0, 6).Value
TextBox25.Text = ActiveCell.Offset(0, 7).Value
TextBox26.Text = ActiveCell.Offset(0, 8).Value
TextBox27.Text = ActiveCell.Offset(0, 9).Value
End If
End If
End Sub


Any and all help is appreciated!:)

thanks,

marty6
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Userform to update rows of data 4
Word macro that can call cells out of Excel for a find and replace 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Variable Defined as Double is entered in cell with only 2 decimal places 2
Code does not error but no copy either 4
entering data into multiple sheets 9
Help me4 2
Need help-For loop 3

Top