Originally Posted by
Paul_Hossler
Unfortunately, with this code I get the error on the line Set rng = Range(addr): Run-time error 1004
Method 'Range' of object '_Global' failed.
Code for userform
Option Explicit
Private Sub CommandButton1_Click()
Set rng1 = Range(RefEdit1.Value)
n1 = Form.TextBox1.Value
Unload Me
End Sub
Private Sub RefEdit1_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)
Dim addr As String, rng1, cell As Range, minimum As Double
addr = RefEdit1.Value
Set rng1 = Range(addr)
minimum = WorksheetFunction.Min(rng1)
For Each cell In rng1
If cell.Value = minimum Then cell.Font.Color = vbRed
Next cell
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub UserForm_Click()
Sheet1.Cells.Font.Color = vbBlack
UserForm1.RefEdit1.Text = Selection.Address
End Sub
For module
Option ExplicitPublic n1 As Integer, rng1 As Range, addr As String
Public Sub M()
Form.Show
Dim rng As Range, n As Integer
n = n1
Set rng = Range(addr)
Debug.Print n
'Debug.Print rng.Addres
End Sub