A form used to pick up values from a worksheet
Excel VBA provides a handy control called RefEdit. If you put a RefEdit control on a modal form, you can easily pick up a range of cells in a worksheet. This is pretty useful when you design a complicated form to achieve a advanced function. However, you cannot use RefEdit control in the form if you design a modalless form. If you use RefEdit control, it will crash and freeze Excel. What a disaster!
There is an alternative way to get around this. The idea is to create a very simple form in which only RefEdit control and a few other controls are included. You can use the form to pick up data and transfer the data to the main form. Please follow the link at the end of the post to download the simple form and VBA code. The procedure used to accept data is listed below. To use the downloaded files, just unzip the file and import the form files in VBA editor.
dim myrange as range
dim VarInCol as boolean
Me.Hide
frmAddVariables.Show
If frmAddVariables.redVariable.Value <> "" Then
Set myrange = Range(frmAddVariables.redVariable.Value)
VarInCol = frmAddVariables.rbtnCol.Value
End If
frmAddVariables.Hide
Me.Show
Download the sample example file with VBA code – pickupdata.zip.
