Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to define a range of cells. Would the following code work?

    Dim rng As Range
    Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A5:A20" )

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, that's the way to set a Range variable in VBA. You can now do things with the variable rng, for example

    rng.Interior.ColorIndex = 3

    or loop through its cells:

    Dim oCell As Range
    For Each oCell In rng
    ...
    Next oCell

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Any idea why I get a "Run-time error '1004': Application-defined or Object-defined error" in this line of the module:

    Sub ShowDialog()

    With frmProperties ' <<< Error shows up here
    .cmbType.RowSource = ""
    .lstName.RowSource = ""
    .lstFinish.RowSource = ""
    .lstProperties_BOL.RowSource = ""
    .lstProperties_EOL.RowSource = ""
    .lstName.MultiSelect = fmMultiSelectMulti
    .lstFinish.MultiSelect = fmMultiSelectMulti
    .lstProperties_BOL.MultiSelect = fmMultiSelectExtended
    .lstProperties_EOL.MultiSelect = fmMultiSelectExtended
    End With

    ' Assign material names to ComboBox
    GetMaterialNames
    ' Show the UserForm
    frmProperties.Show
    End Sub

    When I use the following code in the userform module?

    Private Sub UserForm_Initialize()

    ' Worksheets("Properties Database").Activate
    ' Set rngAllMaterialTypes = ActiveWorkbook. _
    Worksheets("Properties Database"). _
    Range("A5", Range("A65536").End(xlUp)) ' <<<< WORKS when removing the two comment signs

    Set rngAllMaterialTypes = ActiveWorkbook. _
    Worksheets("Properties Database"). _
    Range("A5", Range("A65536").End(xlUp)) ' <<<< DOESN'T WORK

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    Dim wsh As Worksheet
    Set wsh = Worksheets("Properties Database")
    Set rngAllMaterialTypes = wsh.Range(wsh.Range("A5"), wsh.Range("A65536").End(xlUp))

    or

    Dim wsh As [s]Range[/s] Worksheet
    Dim m As Long
    Set wsh = Worksheets("Properties Database")
    m = wsh.Range("A65536").End(xlUp).Row
    Set rngAllMaterialTypes = wsh.Range("A5:A" & m)

  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans. I now get a "Run-time error '13': Type mismatch" at this line:

    With frmProperties


    Do you know why?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the second example I should have had

    Dim wsh As Worksheet

    instead of

    Dim wsh As Range

  7. #7
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764878' date='11-Mar-2009 13:20']In the second example I should have had

    Dim wsh As Worksheet

    instead of

    Dim wsh As Range[/quote]
    That did it. Thanks.

Posting Permissions

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