refedit help

**jlt199** · 03-07-2012, 06:27 PM

I have a form that contains a number of refedit boxes.

When the user selects a range of data for a refedit box, I would like the view of the workbook/sheet to remain the same when they exit the refedit control. It is likely that the data the user will select for each of my refedits will be in close proximity of each other. Currently I can't even get the view to remain on the worksheet, unless the form is called whilst that worksheet is active.

Also is there a definitive list of what each of the actions that can be set on form controls actually mean and relate to? Some of them seem obvious (eg. button_click()), but some less so (eg. button_beforeDropOrPaste())

Thanks

**smuzoen** · 03-08-2012, 01:11 AM

Can you post a sample workbook with your refedit box - I am not entirely sure I understand however you could try something along the lines of

Please Login or Register  to view this content.

This will return you to the active sheet when the form was initialised and the active cell. Is this what you are after.
With respect to a definitive list of procedures for controls I do not know of a website however the easiest way is to do the following
In the VB editor hit F2 - this will bring up the object browser
Select MSForms from the drop down box then from the Classes section select the Control you are interested in - on the right will be the members of that control (so essentially the properties) however next to some of the members is a lightning icon (eg. CommandButton- BeforeDropOrPaste) - select the procedure (the ones with the lightning icon) and RIGHT click on the procedure and select help (do not hit F1 - that will give you NO help) - this will bring up the Microsoft Forms Visual Basic Reference help and it will explain in more detail the procedure giving you a good idea of syntax for members of the controls. Some of the help is not great so in that case google is your friend.
If the suggestion with the first part of your question does not help post a dummy workbook with no sensitive data so I can see what you are seeing. There also is code to around to zoom to certain parts of the workbook/center on an area however it really depends on what you are after.
Some links you might want
Visual Basic for Applications Language Reference for Office 2010

Visual Basic for Applications Language Reference for the 2007 Office System

**jlt199** · 03-09-2012, 01:42 PM

Thanks for your reply. Perhaps I can be more specific in my issue. I have a form that contains a numbers of refedit controls for which I need to enter several ranges. It is likely that the data for each of the ranges is close together. If I activate the form whilst sheet3 of my workbook is active, when the data I want is on sheet1, then everytime I move to the next refedit box the focus returns to sheet3 (the active range when the form was loaded).

Can I, when I leave the first refedit box and select the second one, maintain the focus of the workbook to the selection made in the first refedit box?

Many thanks
Jane

**smuzoen** · 03-10-2012, 03:28 AM

The refedit control is absolutely awful. Here are some rules using the contol
1. Cannot use on a modeless form - if you do it will most likely crash excel. Userforms are modal by default so unless you have set it to modeless then this should not be a problem
2. Only use refedit control on userforms - do not place them inside frames or anything else (multipage forms) - use on a single form only
3. As a general statement you may as well forget about ALL of the refedit events - they basically DO NOT WORK - if you try placing code inside a refedit event then weird things can happen - usually the form will just unload. For example try this. Place 2 refedit controls on a form and add this code to the form

Please Login or Register  to view this content.

When you exit the refedit1 control and go to the second one all that will happen is that the value from the refedit1 control will show in a message box however the worksheet will not be selected. (Remember that the refedit value is a string and NOT a range) - If you then click back into the refedit1 control then the form will probably just unload. If you try the following again just with 2 refedit controls

Please Login or Register  to view this content.

When you exit the first refedit control most likely NOTHING will happen and the form will just unload.
The point is that refedit events are very very unreliable and should be avoided. Essentially all refedit controls are good for is getting the refedit.value and that is about it.
It has also been suggested that you should remove the reference to the refedit control (In the VB editor select tools and then references then untick refedit - to me it seems to make no difference.
So what do you do - I have attached a sample workbook with 3 refedit controls and a command button and have used the following code on the form

Please Login or Register  to view this content.

What this will do is that every time you place a value into the refedit control you push the command button and the region you just selected will stay active - if you don't code for the refedit value as you said in your post the form will just appear on whatever sheet was active at the time the form was loaded. By passing the refedit value (which is a string) and the control to a separate sub it is easier to use multiple refedit controls - you will just have to add some more conditional statements to the command_button sub. This way each refedit control is not visible until the previous refedit control is filled.
The refedit control is a pain to work with - its events are pointless. I hope the above code can be adapted to your particular situation.

**jlt199** · 03-12-2012, 03:59 PM

Thanks a lot for your help. It was really useful, I have accepted that the form won't work exactly as I would like because of the refedits. But then, since it won't be me using it ...