Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Pass a variable to a userform

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, I'm new to userforms hence all the questions! I have a userform which pops up when a cell is selected, it then enters the data into the cell selected. I thought I could just copy the userform and modify slightly for the other 200 cells in the column. Doh!

    I think a better solution would be to use just one form and pass a variable to it, letting it know which cell to enter the details into.
    The code I have for activating the userform is this...

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$E$4" Then Ex1.Show
    End Sub

    Thanks Audiojoe!
    But I need to send that cell to the userform.

    Any ideas? Thanks in advance for any help.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you mean when you say that you need to send that cell to the userform? Do you mean that it's value appears on there somewhere?

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No, I would like it to remember what cell was clicked, store it in a variable, and use that variable to enter the form details back into the clicked cell.



    [ This Message was edited by: Andy Gee on 2002-04-11 08:56 ]

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe if I could just store the Target.Address in a variable then use it when the command button is pressed I could get it to work.

    I tried this with
    cellName = (Target.Address)
    When clicking the cell

    and
    Range(cellName).Value = Textbox1.Value
    when the command button is pressed.
    But I get an error message...
    Method 'Range' of object '_Global' failed

    Please help!

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    juts wondering how can u have spinner in the user form? it doesn't work for me, so i am wondering what have i done wrong?

    i set the cell link to of the spinner to a offset refernce, can any1 please help me on this?


  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't have a spinner! I need to be able to store a variable in one macro and use it in another.
    Give me some more info on what you need and I'll try to help!

    [ This Message was edited by: Andy Gee on 2002-04-11 09:21 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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