Consulting

Results 1 to 3 of 3

Thread: Pass the range from userform to sub of module

  1. #1

    Pass the range from userform to sub of module

    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!

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,662
    Location
    Set rng = Range(addr)
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Quote Originally Posted by Paul_Hossler View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •