Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm help
Hello everyone. I hope someone can help?
I have created a Userform that has 8 input boxes in it. On the first 4 boxes I tell users to input specific info to calculate the specified formula from Userform1. Is there a way to calculate the formula in the same userform? If not how do I extract the info in boxes 1-4 to the spreadsheet to add the formula. In the future I want to hide and unhide parts of the sreadsheet that are not used so it looks cean and orderly, how do you add this to the userform functionality? TIA Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm help
sStr = "#" & trim(textbox1) & "#" & trim(textbox2) & "#" & _
trim(Textbox3) & "#" & trim(Textbox4) & "#" sStr1 = Application.substitute(application.substitute(sStr ,"#",""),".","") if not istr(sStr,"##") and isnumeric(sStr) then Textbox5 = cdbl(Textbox1) + cdbl(Textbox2) ^ cdbl(textbox3) / cdbl(textbox4) End If Worksheets("Sheet5").Range("1:10,12:12,15:20").hid den = True -- Regards, Tom Ogilvy Pete wrote in message ... Hello everyone. I hope someone can help? I have created a Userform that has 8 input boxes in it. On the first 4 boxes I tell users to input specific info to calculate the specified formula from Userform1. Is there a way to calculate the formula in the same userform? If not how do I extract the info in boxes 1-4 to the spreadsheet to add the formula. In the future I want to hide and unhide parts of the sreadsheet that are not used so it looks cean and orderly, how do you add this to the userform functionality? TIA Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm help
Thanks Tom. I am assuming that I need to also put this in
the CommandButton1 that will set off the calculation? But I get a Compile Error stating Sub or function not defined. If Not istr(sStr,..... Any thoughts or am I doing this wrong. Pete W -----Original Message----- sStr = "#" & trim(textbox1) & "#" & trim(textbox2) & "#" & _ trim(Textbox3) & "#" & trim(Textbox4) & "#" sStr1 = Application.substitute(application.substitute (sStr,"#",""),".","") if not istr(sStr,"##") and isnumeric(sStr) then Textbox5 = cdbl(Textbox1) + cdbl(Textbox2) ^ cdbl (textbox3) / cdbl(textbox4) End If Worksheets("Sheet5").Range("1:10,12:12,15:20").hi dden = True -- Regards, Tom Ogilvy Pete wrote in message ... Hello everyone. I hope someone can help? I have created a Userform that has 8 input boxes in it. On the first 4 boxes I tell users to input specific info to calculate the specified formula from Userform1. Is there a way to calculate the formula in the same userform? If not how do I extract the info in boxes 1-4 to the spreadsheet to add the formula. In the future I want to hide and unhide parts of the sreadsheet that are not used so it looks cean and orderly, how do you add this to the userform functionality? TIA Pete . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm help
Pete,
The 'istr' should be 'instr' . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Pete" wrote in message ... Thanks Tom. I am assuming that I need to also put this in the CommandButton1 that will set off the calculation? But I get a Compile Error stating Sub or function not defined. If Not istr(sStr,..... Any thoughts or am I doing this wrong. Pete W -----Original Message----- sStr = "#" & trim(textbox1) & "#" & trim(textbox2) & "#" & _ trim(Textbox3) & "#" & trim(Textbox4) & "#" sStr1 = Application.substitute(application.substitute (sStr,"#",""),".","") if not istr(sStr,"##") and isnumeric(sStr) then Textbox5 = cdbl(Textbox1) + cdbl(Textbox2) ^ cdbl (textbox3) / cdbl(textbox4) End If Worksheets("Sheet5").Range("1:10,12:12,15:20").hi dden = True -- Regards, Tom Ogilvy Pete wrote in message ... Hello everyone. I hope someone can help? I have created a Userform that has 8 input boxes in it. On the first 4 boxes I tell users to input specific info to calculate the specified formula from Userform1. Is there a way to calculate the formula in the same userform? If not how do I extract the info in boxes 1-4 to the spreadsheet to add the formula. In the future I want to hide and unhide parts of the sreadsheet that are not used so it looks cean and orderly, how do you add this to the userform functionality? TIA Pete . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm help
Chip
Am I correct that I need to put a Command Button on the Userform and set this code inside of it? If so nothing happens. But if I take out the ~InStr(sStr, "##") And~ I get a calculation in textbox 5, but it is not the right answer? Puzzeling.... Pete -----Original Message----- Pete, The 'istr' should be 'instr' . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Pete" wrote in message ... Thanks Tom. I am assuming that I need to also put this in the CommandButton1 that will set off the calculation? But I get a Compile Error stating Sub or function not defined. If Not istr(sStr,..... Any thoughts or am I doing this wrong. Pete W -----Original Message----- sStr = "#" & trim(textbox1) & "#" & trim(textbox2) & "#" & _ trim(Textbox3) & "#" & trim(Textbox4) & "#" sStr1 = Application.substitute(application.substitute (sStr,"#",""),".","") if not istr(sStr,"##") and isnumeric(sStr) then Textbox5 = cdbl(Textbox1) + cdbl(Textbox2) ^ cdbl (textbox3) / cdbl(textbox4) End If Worksheets("Sheet5").Range("1:10,12:12,15:20").hi dden = True -- Regards, Tom Ogilvy Pete wrote in message ... Hello everyone. I hope someone can help? I have created a Userform that has 8 input boxes in it. On the first 4 boxes I tell users to input specific info to calculate the specified formula from Userform1. Is there a way to calculate the formula in the same userform? If not how do I extract the info in boxes 1-4 to the spreadsheet to add the formula. In the future I want to hide and unhide parts of the sreadsheet that are not used so it looks cean and orderly, how do you add this to the userform functionality? TIA Pete . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm help
If you are using the formula I used, it was made up. You never said what
formula you wanted to use. This works, but still uses my sample formula: Private Sub CommandButton1_Click() sStr = "#" & Trim(TextBox1) & "#" & Trim(TextBox2) & "#" & _ Trim(TextBox3) & "#" & Trim(TextBox4) & "#" sStr1 = Application.Substitute(Application.Substitute(sStr , "#", ""), ".", "") If InStr(sStr, "##") = 0 And IsNumeric(sStr1) Then TextBox5 = CDbl(TextBox1) + CDbl(TextBox2) ^ CDbl(TextBox3) / CDbl(TextBox4) End If End Sub -- Regards, Tom Ogilvy Pete wrote in message ... Chip Am I correct that I need to put a Command Button on the Userform and set this code inside of it? If so nothing happens. But if I take out the ~InStr(sStr, "##") And~ I get a calculation in textbox 5, but it is not the right answer? Puzzeling.... Pete -----Original Message----- Pete, The 'istr' should be 'instr' . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Pete" wrote in message ... Thanks Tom. I am assuming that I need to also put this in the CommandButton1 that will set off the calculation? But I get a Compile Error stating Sub or function not defined. If Not istr(sStr,..... Any thoughts or am I doing this wrong. Pete W -----Original Message----- sStr = "#" & trim(textbox1) & "#" & trim(textbox2) & "#" & _ trim(Textbox3) & "#" & trim(Textbox4) & "#" sStr1 = Application.substitute(application.substitute (sStr,"#",""),".","") if not istr(sStr,"##") and isnumeric(sStr) then Textbox5 = cdbl(Textbox1) + cdbl(Textbox2) ^ cdbl (textbox3) / cdbl(textbox4) End If Worksheets("Sheet5").Range("1:10,12:12,15:20").hi dden = True -- Regards, Tom Ogilvy Pete wrote in message ... Hello everyone. I hope someone can help? I have created a Userform that has 8 input boxes in it. On the first 4 boxes I tell users to input specific info to calculate the specified formula from Userform1. Is there a way to calculate the formula in the same userform? If not how do I extract the info in boxes 1-4 to the spreadsheet to add the formula. In the future I want to hide and unhide parts of the sreadsheet that are not used so it looks cean and orderly, how do you add this to the userform functionality? TIA Pete . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA userform | Excel Worksheet Functions | |||
UserForm | Excel Discussion (Misc queries) | |||
UserForm Help | Excel Discussion (Misc queries) | |||
Use same UserForm | Excel Programming | |||
userform | Excel Programming |