PDA

View Full Version : Solved: Help using the RefEdit Control



mbake16
02-03-2009, 06:35 AM
I'm trying to use "RefEdit" to allow an end user to select a range of cells to be used to run additional procedures within a sub procedure.

My code allows the end user to populate the RefEdit Control but once the UserForm is closed it generates an error on the line in which I try to assign the RefEdit value to a range variable. Below is an example of the code which is also included in the attached example:

Sub RefEdit_Example()

Dim Rng1 As Range
Dim RngCell As Range

UserForm1.RefEdit1.ControlTipText = "Select Range of Cells"
UserForm1.Show

Set Rng1 = Range(UserForm1.RefEdit1.Value)

For Each RngCell In Rng1
Set RngCell.Font.Bold = True

Next

End Sub

Any help you could provide would be great!


Thanks,

Matt

nst1107
02-03-2009, 08:02 AM
You get this error because the code doesn't step to that line until after the userform is unloaded. When the userform is unloaded, all the parameters of its controls are reset to their values set at designtime, so the value of RefEdit1 will be "". Try using a command button to execute the procedures associated with the refedit before the userform is unloaded, or put the procedures in the _Terminate() event of the userform.

mbake16
02-03-2009, 10:04 AM
AWESOME!! Thank you. I added a control button and dropped the For Each Loop in there, worked great!