PDA

View Full Version : Solved: Userform to use IF formula and input result to ws



protegeone
10-21-2005, 07:24 AM
Hi Everyone,

I'm even more at loss with userforms then with VBA, so your utmost patince please.

What I'm trying to achive is

Please see my attachment ( to get more sense out of what I'm about )
Taking "Senior Supervisor" wsheet, Userform is called by button in cell ( e.g. L6 ), having 2 comboboxes.
After selecting choices from both boxes ( should choose from both at all times )
the following formula should be run in userform
IF(ComboBox1<=0;0;M3/Combobox1*(Combobox1-Combobox2))

result then should be copied to cell M6.
(Due to being merged cells in other macro I've only been able to copy to it calling it range. e.g. M3 as M3:N5 or taking destination cell M6 as M6:N7, but this might be only me, absolute beginner )

To complicate it even further as I would have 3 buttons in all calling this userform, the question is is there a way to have userform first checking downward whether destination cell is empty or not and only copying result to the next empty cell from the available three, instead of making 3 identical userforms with only changing destination cell address.

Hope someone out there would be able to get some meaning out of this and would help.

Thanks

Protege

mdmackillop
10-22-2005, 01:38 PM
Hi Protege,
I've revised your Userform to add the result into the first available of the three cells. I'm not sure of the purpose of the other two buttons. Do you want them to return the value in the corresponding cell?
I also looked at the alap button which was crashing. This will return the value MA into cell M3 , which doesn't make sense to me, but seems to be what was intended.
Regards
MD

protegeone
10-22-2005, 02:51 PM
Hi MD,

Thanks very much. It's flawless.
I was just about giving up hope. I went most probably in the wrong direction in
trying along Range("M6:N7").Value = Application.WorksheetFunction =

and its variations, but the best I was at having the formula copied to the cell, along with the result.

The other buttons would call the same userform, but placing result in the adjacent cell. ( trying to make it visually confirming ).

New question:
Is there way letting userform know which button called it and having it place result in the respective cell iso checking whether cell is empty before placing result?

It's beyond me why alap button results in "MA" in destination cell, but it is
one macro which went o.k. the first try http://vbaexpress.com/forum/images/smilies/045.gif and it still does its job.
Crashing might be because I've removed most of the source data before posting the file and as I'm new to this, I'm not yet into error handling, but seems to be the just time to start on that as well..

Cheers

Protege

mdmackillop
10-22-2005, 03:10 PM
Revised to use userform with three buttons. I added a label with Visible set to false and set a caption of 1, 2 or 3 from the calling button.
What is the alap button intended to do?
The problems I noticed were Dim Found_Range as Range was missing, and the target sheet name had been changed.

protegeone
10-23-2005, 05:49 AM
Thanks a lot again.

Alap button gets value assigned to name in cell A8 from sheet "valami", which will be hidden when project is done.
User chooses name from VL list and clicks on alap button, which will gets value assigned to that specific name from the hidden "valami" sheet.

Now I know why you're getting "MA" all the time: at the moment this search and fetch is set to deal with sheet "z?r?s" iso "valami".

protegeone
10-24-2005, 01:51 PM
@ mdmackillop


All works fine. Marking thread solved.

Thanks for your help

Protege