I'm getting there. Three days and still working on this form
I have the following text boxes and combo boxes.
cboxServiceType (combobox)
txtQty
txtExcess.
If the value in cboxService = a specific value - ie "Excess"
I would like the following formula to be entered into the txtExcess box:-
=sum txtQty - 4000.
Is this at all possible? If so how do I write it and where? I assume in the cboxServiceType_Change() Private sub??
By the way I am using Excel 2011 for Mac in case that has any impact.
I can't comment on the Mac ramifications. But the code in the cboxServiceType_Change() macro would be something like this... several examples are shown so you can see how you can handle various values:
If the Me.Value reference doesn't work, then replace that with cboxServiceType.Value
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks for the above response, however I get a Run-time error '13' -Type mismatch at:
txtExcessTank.Value = txtQty.Value - 4000.
Have I written it write??Please Login or Register to view this content.
Last edited by mariannehislop; 09-28-2014 at 12:42 PM.
It looks ok. At the moment the error occurs, what is the current value of the text box?
Can there ever be more than one value for this box? The SELECT CASE method is really only needed if there can be more than one possible value and result you want.
cboxServiceType can be more that one value. When "Septic System >4000" is selected from the list the txtExcessTank is Enabled and I want the formula to be entered. This formula is only relevant when item "Septic System >4000" is selected, otherwise it is empty.
I have managed to unlock it with:
Which this part is working fine.Please Login or Register to view this content.
So when the error occures the txtExcessTank box is blank.
I meant the value of the txtQty
Maybe:
OrPlease Login or Register to view this content.
)Please Login or Register to view this content.
Last edited by JBeaucaire; 09-28-2014 at 05:55 PM. Reason: Added missing CODE tags.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Strictly speaking, when doing arithmetic with values from TextBoxes, one should convert the string into a number.
In this regard, Mac's are strict.
Please Login or Register to view this content.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Mike, rattling around in my brain is a recollection that CDbl respects intenational settings, and Val doesn't.
Last edited by shg; 09-28-2014 at 04:23 PM.
Entia non sunt multiplicanda sine necessitate
Yes, but CDbl("x") throws an type mismatch error. Val("x") = 0.
Nevermind!
Last edited by xladept; 09-28-2014 at 05:48 PM.
Thanks Mike. I'm very new to VB coding so not quite following.
I have initialised the cboxServiceType list with:
This is working.Please Login or Register to view this content.
I have this code populating my second combobox txtRate
Now I would like a formula to go into the txtExcessTank box that saysPlease Login or Register to view this content.
If cboxServiceType>4000, then
txtExcessTank = formula (txtQty) - 4000
So if the txtQty was 5000, the txtExcessTank should display 1000
I have added the following code as instructed by you.
However, when I run the form I get the following errorPlease Login or Register to view this content.
"Run-time error '424':
Object required
??
Last edited by mariannehislop; 09-29-2014 at 08:45 PM.
It looks like were I have txtExcess, the actual name of your text box (txtExcessTank) should be used.
Thanks Mike. I am using the name txtExcessTank and it still has the same error.
I have given up on this task and decided to include a formula into a table on the sheet that is working fine. Though would be useful to know for the future.
Thanks for your effort anyway. Much appreciated
Can't workPlease Login or Register to view this content.
Might workPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks