[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.
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)
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
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"
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
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".
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
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.
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
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
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
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