Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    form isn't unloading (Excel 2000)

    Hi all,

    I have a userform that when loaded in one of the lables I use the activecell.value as the caption, When I reload the form after it has been used, on reloading sometimes the value in the caption hasn't cleared itself. On initializing the form FrmFORM1.label.caption = activecell.value, it doesn't always pick up the value of the activecell, I have even tried setting the control on terminate, but still it does not help that much. Is there another way to capture the value of the activecell? Maybe I have to destroy something? <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

    Thanks,
    Darryl.

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

    Re: form isn't unloading (Excel 2000)

    Try putting a command button cmdClose on the form with caption Close and the following On Click event:

    Private Sub cmdClose_Click()
    Unload Me
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: form isn't unloading (Excel 2000)

    In xl97, I had no problem reading the activecell.value into a label named "label" on a userform named "frmFORM1" whenever the form was loaded.

    Is there more to the problem?
    Steve

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: form isn't unloading (Excel 2000)

    Thanks Steve and Hans,

    Hans had the fix, what is the difference between unload frmname, and unload me?

    Darryl.

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

    Re: form isn't unloading (Excel 2000)

    In the code module associated with the userform, you can refer to the form as Me. So in this module, Me is equivalent to frmName. In other modules, you must use frmName.

    Similarly, in the code module associated with a worksheet, you can refer to the worksheet as Me, for example Me.Range("A1").Value = 37. In other modules, you'd have to use Worksheets("SheetName").Range("A1").Value = 37.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: form isn't unloading (Excel 2000)

    Unload Me will still work if you change the formname. If you are explicit about the name, you have to be correct.

    Steve

  7. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: form isn't unloading (Excel 2000)

    Thanks guys...another tip in the hat.[img]/forums/images/smilies/smile.gif[/img]

  8. #8
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: form isn't unloading (Excel 2000)

    I thought the problem was solved with the Help of Hans and Steve, I figured out that I still had the problem of loading the information into the label on the form, What I realized yesterday is that the form wasn't loading correctly, as it turned out by setting the form before cancel, solved, my problem.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    If Not Intersect(Target, Range("a5:A17")) Is Nothing Then
    Load FRMbook
    FRMbook.Show
    Cancel = True
    End If
    end sub

    new code

    If Not Intersect(Target, Range("a5:A17")) Is Nothing Then
    FRMbook.Label1 = ActiveCell.Value ' <-------added
    Load FRMbook
    FRMbook.Show
    Cancel = True
    End If
    end sub

    Thanks
    Darryl.

Posting Permissions

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