Need help with userform....please....

[marty6]

Hi,

I'm very, very new at VBA and excel. I have a problem here at work an
I'm in need of some help. I would like to create a popup menu and b
able to input section numbers which are attached to adjacent cells.
The popup menu will have seven input textboxes under the add column an
seven input textboxes under the subtract column. Here's an example:

section number of items
1001.01 10
1001.02 11
1001.03 09


If I typed in 1001.01 in one of the add textboxes, the number ite
increases by one. If I typed in 1001.01 in the subtract textbox, th
item would decrease by one. The rest of the spreadsheet has forumla
for column totals. So, that being said, can someone help me i
developing an input box(userform) that pops up? I did go to the M
excel web page in order to learn about how to create a basic userform.
But, what I would like to do is beyond my knowledge. Any help i
appreciated.

BTW. I have 560 section numbers and an equal number of "number o
Items" also.

Thank you,

marty

 

[Bob Phillips]

Try something like

Private Sub CommandButton1_Click()

IncDec txt1Add, True
IncDec txt1Subtract, False
'etc.

End Sub

Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long

iItem = Evaluate("Match(" & textbox.Text & ", A1:A10, 0)")
If iItem > 0 Then
If Increment Then
Cells(iItem, "B").Value = Cells(iItem, "B").Value + 1
Else
Cells(iItem, "B").Value = Cells(iItem, "B").Value - 1
End If
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[marty6]

Bob Phillips,

Thank you for the reply!

I still need alittle help with this though. Could you walk me through
this example?

Would it be like this?

Private Sub Commandbutton1_Click()

IncDec txt1Add, True
IncDec txt1Subtract, False

IncDec txt2Add, True
IncDec txt2Subtract, False

IncDec txt3Add, True
IncDec txt3Subtract, False

etc... until all 14 textboxes are written here?

****the IncDec txt1 refers to the textfields correct?**** So, if
have 14 fields, I'll have 28 lines here in this part?

End Sub

Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long


iItem = Evaluate("Match(" & textbox.Text & ", A1:A10, 0)")
If iItem > 0 Then
If Increment Then
Cells(iItem, "B").Value = Cells(iItem, "B").Value + 1
Else
Cells(iItem, "B").Value = Cells(iItem, "B").Value - 1
End If
End If

End Sub

****In this part would I do the following as an example?*****

iItem=Evaluate("Match(" &textbox1.Text & ", B3, 0)")
if iItem > 0 Then
if Increment Then
Cells(iItem, "B4").Value = Cells(item, "B4").Value +1
Else
Cells(item, "B4").Value = Cells(item, "B4").Value -1
End If
End If

End Sub

My 1001.01 is located in Cell B3 on the spreedsheet. And the amoun
that I want to add to and subtract from is in Cell B4. So, when I typ
in the number 1001.01 in the textbox on the userform, the number i
Cell B4 will increase.

Here's another question:

If textbox1 through textbox7 are under the ADD column on the userfor
and textbox8 through 14 are under the SUBTRACT column, how would tha
be written above? would it be like the following?

partial example

Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long


iItem = Evaluate("Match(" & textbox1.Text & ", B3, 0)")
If iItem > 0 Then
If Increment Then
Cells(iItem, "B4").Value = Cells(iItem, "B4").Value + 1
Else
Cells(iItem, "B4").Value = Cells(iItem, "B4").Value - 1
End If
End If
iItem = Evaluate(Match(" & textbox1.Text & ", C3, 0)")
if iItem > 0 Then
Cells(item, "C4").Value = Cells(iItem, " C4").Value +1
Else
Cells(iItem, "C4").Value = Cells(item, "C4").Value -1
End If
End If
End Sub

On my spreedsheet(classdb1) I have the following(small example):

1001.01 is in cell B3 and #amount(let's say 10) is in Cell B4.
1001.02 is in cell C3 and #amount(23) is in Cell C4
1001.03 is in cell D3 and #amount (8) is in Cell D4


Bob, please post back and tell me if this is correct or not and if yo
can, correct the example and then I'll start typing it up. I'
thinking that this is going to get big.

Thank you,

marty

 

[marty6]

Hi Bob,

When I ran the Debug, I got the following error:

compile error:
ByRef Argument type mismatch

What was highlighted in the window:

txt1Add




Thanks,

Marty

 

[Bob Phillips]

Bob Phillips,

Thank you for the reply!

I still need alittle help with this though. Could you walk me throught
this example?

Would it be like this?

Private Sub Commandbutton1_Click()

IncDec txt1Add, True
IncDec txt1Subtract, False

IncDec txt2Add, True
IncDec txt2Subtract, False

IncDec txt3Add, True
IncDec txt3Subtract, False

etc... until all 14 textboxes are written here?

****the IncDec txt1 refers to the textfields correct?**** So, if I
have 14 fields, I'll have 28 lines here in this part?

End Sub

Yes that is what you need to do.

Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long


iItem = Evaluate("Match(" & textbox.Text & ", A1:A10, 0)")
If iItem > 0 Then
If Increment Then
Cells(iItem, "B").Value = Cells(iItem, "B").Value + 1
Else
Cells(iItem, "B").Value = Cells(iItem, "B").Value - 1
End If
End If

End Sub

****In this part would I do the following as an example?*****

iItem=Evaluate("Match(" &textbox1.Text & ", B3, 0)")
if iItem > 0 Then
if Increment Then
Cells(iItem, "B4").Value = Cells(item, "B4").Value +1
Else
Cells(item, "B4").Value = Cells(item, "B4").Value -1
End If
End If

End Sub

No, not

iItem=Evaluate("Match(" &textbox1.Text & ", B3, 0)")

as that is only testing against on e cell, an d I thought you wanted to
check a range of values, so it should be

iItem=Evaluate("Match(" &textbox1.Text & ", B3,:B20 0)")

or wherever the numbers end. Otherwise you would just cjheck

If textbox.Text = Range("B3").Value Then

You might want to add the sheet in there as well

iItem=Evaluate("Match(" &textbox1.Text & ", Sheet1!B3,:B20 0)")

My 1001.01 is located in Cell B3 on the spreedsheet. And the amount
that I want to add to and subtract from is in Cell B4. So, when I type
in the number 1001.01 in the textbox on the userform, the number in
Cell B4 will increase.

What should happen if the number is 1001.02?

Here's another question:

If textbox1 through textbox7 are under the ADD column on the userform
and textbox8 through 14 are under the SUBTRACT column, how would that
be written above? would it be like the following?

partial example

Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long


iItem = Evaluate("Match(" & textbox1.Text & ", B3, 0)")
If iItem > 0 Then
If Increment Then
Cells(iItem, "B4").Value = Cells(iItem, "B4").Value + 1
Else
Cells(iItem, "B4").Value = Cells(iItem, "B4").Value - 1
End If
End If
iItem = Evaluate(Match(" & textbox1.Text & ", C3, 0)")
if iItem > 0 Then
Cells(item, "C4").Value = Cells(iItem, " C4").Value +1
Else
Cells(iItem, "C4").Value = Cells(item, "C4").Value -1
End If
End If
End Sub

On my spreedsheet(classdb1) I have the following(small example):

1001.01 is in cell B3 and #amount(let's say 10) is in Cell B4.
1001.02 is in cell C3 and #amount(23) is in Cell C4
1001.03 is in cell D3 and #amount (8) is in Cell D4

No I assumed rows, it should be columns

Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long

iItem = Evaluate("Match(" & textbox.Text & ", B3, 0)")
If iItem > 0 Then
If Increment Then
Cells(3,iItem+1).Value = Cells(3,iItem+1).Value + 1
Else
Cells(3,iItem+1).Value = Cells(3,iItem+1).Value - 1
End If
End If

End Sub

 

[Bob Phillips]

Marty,

I think that was because I wasn't allowing for testing against a string. Are
B3, C3 string values? If so this is tested and should work

Private Sub CommandButton1_Click()

IncDec txt1Add, True
IncDec txt1Subtract, False
'etc.
IncDec txt14Add, True
IncDec txt14Subtract, False

End Sub

Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long

iItem = Evaluate("Match(""" & textbox.Text & """, B3:H3, 0)")
If iItem > 0 Then
With Worksheets("Sheet1")
If Increment Then
.Cells(4, iItem + 1).Value = .Cells(4, iItem + 1).Value + 1
Else
.Cells(4, iItem + 1).Value = .Cells(4, iItem + 1).Value - 1
End If
End With
End If

End Sub

Private Sub UserForm_Click()

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[marty6]

Hi Bob,

I have started over and I think that I am close:


I went back to two columns, Column A and Column B. The new excel boo
is called "book31" respectively.

In excel, Column A and b look like this:

Column A Column B
1001.01 10
1001.02 15
1001.03 20
1001.04 06

Here's the userform formula:

Private Sub CommandButton1_Click()
IncDec (txt1Add), True
IncDec (txt1Subtract), False

IncDec (txt2Add), True
IncDec (txt2Subtract), False

End Sub
Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long


iItem = Evaluate("Match(" & TextBox1.Text & ", A1:A4, 0)")
If iItem > 0 Then
With Worksheets("book31")
If Increment Then
Cells(iItem, "B").Value = Cells(iItem, "B").Value + 1
Else
Cells(iItem, "B").Value = Cells(iItem, "B").Value - 1
End If
End With
End If

End Sub

Private Sub CommandButton2_Click()
End
End Sub



ok, here's the issue. I had to add () marks around the txt1Add an
txt1Subtract to make the compile error: byref argument type mismatch g
away. Now, I after adding the () marks, I receive the following erro
message: Run time error '424' object required.
After debugging, the highlighted yellow line is the first line in th
programming: IncDec (txt1Add), True

Your thoughts needed.....

Thank you,

marty6

 

[marty6]

Hi Bob,

I had a little progress today. Here's what I've got so far:

Private Sub CommandButton1_click()

IncDec Txt1Add, True
IncDec Txt2Add, True
IncDec Txt3Subtract, True
IncDec Txt4Subtract, True
End Sub


Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long

iItem = Evaluate("Match(" & Txt1Add.Text = "1001.01" & ",B1 +1, 0)")
If iItem > 0 Then
If Increment Then
Cells(1, iItem).Value = Cells(1, iItem).Value + 1
End If
End If
iItem = Evaluate("Match(" & Txt2Add.Text = "1001.01" & ",1, 0)")
If iItem > 0 Then
If Increment Then
Cells(1, iItem).Value = Cells(1, iItem).Value + 1
End If
End If
iItem = Evaluate("Match(" & Txt3Subtract = "1001.01" & "1, 0)")
If iItem > 0 Then
If Increment Then
Cells(1, iItem).Value = Cells(1, iItem).Value - 1
End If
End If
iItem = Evaluate("Match(" & Txt4Subtract = "1001.01" & ", 1, 0)")
If iItem > 0 Then
If Increment Then
Cells(1, iItem).Value = Cells(1, iItem).Value - 1
End If
End If

End Sub



Private Sub CommandButton3_Click()
End
End Sub

_______________________________________________

So far, I did get rid of some of the errors. If you noticed that ther
is "1001.01". For some reason the debugging program was asking fo
this. I find this interesting. Other than that the userform stil
does not work.

Can we start over? I'm getting alittle lost at this point. I spent
hrs yesterday trying to figure it out.

Here's what I have in this arrangement. If you could, could you follo
it in your excel program and see what you come up with as a possibl
solution:

Column A
In cell A1 is 1001.01
In cell A2 is 1001.02

Column B
In cell B1 is 10
In cell B2 is 10

Create a userform with 4 inputboxes(textboxes)
two boxes per column. The heading of the left column will be "Add" an
the heading for the right column is "subtract"
Name your userboxes: (1st one on the upper left as "txt1Add", 2nd on
on the bottom left is "txt2Add". 3rd one on the upper right i
"txt3Subtract", and finally "txt4Subtract" for the bottom right.

Then create two bottons on the bottom of the userform:
"Enter" and "Exit".

What I'm trying to obtain is the ability to type in the section numbe
"1001.01" in either txt1Add or txt2Add in order to increase the numbe
from 10 to 11. Also, trying just the opposite, typing in "1001.01" i
either txt3subtract or txt4subtract in order to go from 10 to 9. I'
also trying to do this with any section number.

I guess I got overwhelmed and confused on this part of the process.

Any of your help is much appreciated

marty

 

[Bob Phillips]

Marty,

I posted this in response to your other question

Private Sub CommandButton1_Click()
IncDec txt1Add, True
IncDec txt1Subtract, False
IncDec txt2Add, True
IncDec txt2Subtract, False
IncDec txt3Add, True
IncDec txt3Subtract, False
IncDec txt4Add, True
IncDec txt4Subtract, False
IncDec txt5Add, True
IncDec txt5Subtract, False
IncDec txt6Add, True
IncDec txt6Subtract, False
IncDec txt7Add, True
IncDec txt7Subtract, False
End Sub

Private Sub IncDec(textbox As msforms.textbox, Increment As Boolean)
Dim iItem As Long

On Error GoTo incdec_exit
iItem = Evaluate("Match(" & textbox.Text & ",A1:H1, 0)")
If iItem Then
If Increment Then
Cells(2, iItem).Value = Cells(2, iItem).Value + 1
Else
Cells(2, iItem).Value = Cells(2, iItem).Value - 1
End If
End If
incdec_exit:
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)