Results 1 to 15 of 15

Thread: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    UK
    Posts
    21

    Resolved [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    I have:
    Private Sub Worksheet_Change(ByVal Target As Range)
    myRow = Target.Row

    And that can be used in things like
    BriefCell = Range("C" & myRow)

    which works fine.

    But I now have a Userform with an "OK" click were I want the TextBox.value to be placed in Cell ("C" & myRow) but I can't get myRow to be recognised here and I can't seem to generate an equivalent.

    So to explain the overall objective, I place my cursor in cell Z4 and enter data, if there's nothing in Cell C4 I get a form pop-up which says you need a value in C4 and a text box to enter that value -> OK and the cell C4 should populate. It works with an absolute reference but I need to do it with the variable of Target.Row.

    In case it's relevant I have the first bit on a worksheet headed Sheet10(Code) and the form instructions on a sheet entitled UserForm2(code), I'm presuming that the one can't read the variable from the other. Digging around it maybe something to do with private -v- public but I got a bit lost understanding how to do that.
    Last edited by Peter_B; Feb 18th, 2015 at 11:21 AM.

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    UK
    Posts
    21

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    Seem to have the same issue in reverse.

    Public Sub CmdOK_Click()
    Answer = MsgBox("You have selected " + UserForm1.ListBox1.Value, vbOKCancel)
    If Answer = vbCancel Then Exit Sub '
    Unload Me
    End Sub

    Where I want UserForm1.ListBox1.Value read back to the main Sub to be tested against in if-then instructions.

    If UserForm1.ListBox1.Value = "Guidance" Or UserForm1.ListBox1.Value = "Information" Then
    Answer = MsgBox("You have selected " + UserForm1.ListBox1.Value, vbOKCancel)

    The message does not output the selected value.

    Peter

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    Define myRow as a module level variable, as you were starting to get at near the bottom of post 1.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    try like
    Answer = MsgBox("You have selected " & UserForm1.ListBox1.Text, vbOKCancel)

    to clarify what bryce posted
    dimension myrow at the very top of a general module as public

    public myrow as long, (could be integer or variant depending on requirement)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    UK
    Posts
    21

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    Thanks for your help so far. I'm not sure if I'm understanding this correctly or not: as it does not seem to work I presume not.

    See attached screen grab. These are what I think are the two relevant coding pages I have. I've tried changing long to string and integer.
    I also tried creating a Module1 with the single declaration of "Public Myrow As String"

    There should be a number (Myrow) in the msgBox.

    Name:  Capture.jpg
Views: 98
Size:  34.9 KB


    Peter

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    And you've entered something in a cell in Column 6 (meaning: you've triggered the Change-Event)?
    Why don't you just put a breakpoint inside the event, then you can see, if/when it's triggered
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    UK
    Posts
    21

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    Not sure about breakpoints I just get the message : not allowed in this line!

    However, if I enable the msgBox myRow in the upper page then enter data to F5 (say) then I get the first message "5" as expected.

    I then OK that and From 2 runs which has the click which generates the second msgBox myRow which then displays [blank].

    I've also put a third msgBox myRow in the upper page towards the end after the form is called just to see what it thinks later and it confirms the variable as still recording "5".

    Peter

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    And there is your Problem:

    Your Variable "Myrow" is Public from the Worksheet-Object, meaning:
    It works like a property of the Worksheet (Sheet10 in your case)

    You either have to address it in your Userform2-Click-Event of the Button with "MsgBox Sheet10.Myrow"
    or put the Variable-Declaration as Public into a Standard-module, then it's public for the whole workbook (IMO the better way)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    UK
    Posts
    21

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    OK,

    So if I use MsgBox Sheet10.Myrow I now get the answer 0 (not 5) which is at least something.

    To create a Module I've gone "insert Module" and get Module1 where I simply enter "Public MyRow As Long", running that I now get 0 as well.
    So the question is why is it losing the value?

    Tried String but that returned blank and Integer 0.

    Peter

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    Did you delete your declaration at the head of the worksheet?
    The Declaration of MyRow should be only once within the whole workbook

    EDIT: I've attached a very small sample for you, taking your intial setup as a guideline
    Attached Files Attached Files
    Last edited by Zvoni; Feb 18th, 2015 at 10:10 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    UK
    Posts
    21

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    Yes and just to be sure I created a different one of


    Dim test As String
    test = "fred"


    Public test As String


    and tried to output that as a msgBox under the click to no avail

    Peter

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    Look at my attachment.
    Enter something in a cell in Column "F" and follow the code
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    UK
    Posts
    21

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    Brilliant: that works so I have a reference point to work from.
    Thank you very much for your patience and help.

    Peter

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    if you dimension a variable with the same name, within a procedure, as your global variable, it will override the global variable within that procedure, as shows in your code from post #5, so no value would be assigned to the global variable, the value assigned to the local variable would be lost (go out of scope) when the procure completes
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    UK
    Posts
    21

    Re: [Excel 2010 - VB 7.1] finding "TargetRow" for User Form

    That was it - thanks.

    Peter

Posting Permissions

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



Click Here to Expand Forum to Full Width