Thread: Need help on Update Button on Userform
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
marty6[_19_] marty6[_19_] is offline
external usenet poster
  
Posts: 1
Default Need help on Update Button on Userform
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


---
Message posted from http://www.ExcelForum.com/

Reply With QuoteReply With Quote