PDA

View Full Version : [SOLVED] Pass the range from userform to sub of module



alex009988
07-10-2019, 08:29 AM
Hello.
I've made the userform with input range and value input and ok button. However, I havent found a way how to pass the range from userorm to module correctly.
Here is my code of 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

and module code


Option Explicit
Public n1 As Integer, rng1 As Range, addr As String
Public Sub M()
Form.Show
Dim rng As Range, n As Integer
n = n1
'rng = Range(addr)
Debug.Print n
End Sub

What's wrong with rng = Range(addr)? Thanks in advance!

Paul_Hossler
07-10-2019, 09:31 AM
Set rng = Range(addr)

alex009988
07-10-2019, 12:03 PM
Set rng = Range(addr)

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