Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
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 " wrote in message ... Hi, I'm very, very new at VBA and excel. I have a problem here at work and I'm in need of some help. I would like to create a popup menu and be able to input section numbers which are attached to adjacent cells. The popup menu will have seven input textboxes under the add column and 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 item increases by one. If I typed in 1001.01 in the subtract textbox, the item would decrease by one. The rest of the spreadsheet has forumlas for column totals. So, that being said, can someone help me in developing an input box(userform) that pops up? I did go to the MS 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 is appreciated. BTW. I have 560 section numbers and an equal number of "number of Items" also. Thank you, marty6 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
"marty6 " wrote in message ... 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, please post back and tell me if this is correct or not and if you can, correct the example and then I'll start typing it up. I'm thinking that this is going to get big. Thank you, marty6 --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
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 " wrote in message ... 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, Marty6 --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
Hi Bob,
Thanks for the reply. I just got back from work. I'll post back in while to confirm the results. Thank you for your help...... marty -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
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 -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
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 -- Message posted from http://www.ExcelForum.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with userform....please....
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) "marty6 " wrote in message ... 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 there is "1001.01". For some reason the debugging program was asking for this. I find this interesting. Other than that the userform still does not work. Can we start over? I'm getting alittle lost at this point. I spent 5 hrs yesterday trying to figure it out. Here's what I have in this arrangement. If you could, could you follow it in your excel program and see what you come up with as a possible 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" and the heading for the right column is "subtract" Name your userboxes: (1st one on the upper left as "txt1Add", 2nd one on the bottom left is "txt2Add". 3rd one on the upper right is "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 number "1001.01" in either txt1Add or txt2Add in order to increase the number from 10 to 11. Also, trying just the opposite, typing in "1001.01" in either txt3subtract or txt4subtract in order to go from 10 to 9. I'm 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 marty6 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm | Excel Worksheet Functions | |||
UserForm | Excel Worksheet Functions | |||
Userform | Excel Discussion (Misc queries) | |||
UserForm 2 | Excel Programming | |||
UserForm | Excel Programming |