Pass Worksheet_Change Target Value to Userform

zoog25

Active Member
Joined
Nov 21, 2011
Messages
331
Hello All,

Here is my situation. I have a Worksheet_Change Event that tracts when Range AC has data entered. I have a YesNo message box that pops up asking the user if they want a special document to be drafted. If they select yes, then a userform will pop up with three option select and a button to Draft documents. The following code for the button looks like this.

Code:
Private Sub cmdbDraft_Click()


If OB_IMP_AGR.Value = True Then
    Call IMP_AGR(Target.Row)
End If


If OB_PRI_AGR.Value = True Then
    Call PR_AGR(Target.Row)
End If


If OB_Maint_AGR.Value = True Then
    Call Maint_AGR(Target.Row)
End Sub
My issue is how do i pass the information of Work_Change (Target as Range) to the userform in order to use the number of the row in order to have it draft one of the above documents. Please let me know if there is anything else that i can help you with. The userform is called "UF_AGR_Selection". Thank you.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,561
How about declaring a public variable (as long) then setting that to the row number during the w/s_change event...
Code:
Dim rw as long
rw = Target.Row
...then calling rw when you need it?
Code:
Private Sub cmdbDraft_Click()


If OB_IMP_AGR.Value = True Then
    Call IMP_AGR(rw)
End If


If OB_PRI_AGR.Value = True Then
    Call PR_AGR(rw)
End If


If OB_Maint_AGR.Value = True Then
    Call Maint_AGR(rw)
End Sub
As long as both the msgbox and userform haven't been displayed modelessly, nothing else should have made a _change event in the sheet.
Just might need to change the rw data type, depending upon what "IMP_AGR" requires (or vice versa).
 
Last edited:

zoog25

Active Member
Joined
Nov 21, 2011
Messages
331
It isn't working. I keep a compliar error on the rw variable in the userform. Here is the code that leads to the process.

Code:
Public rw As Long


Private Sub Worksheet_Change(ByVal Target As Range)


'Disable other sheet Events
Application.EnableEvents = False


If Target.Count > 1 Then Exit Sub


'Initiate Work
Dim FMws As Worksheet


Set FMws = ThisWorkbook.Worksheets("Final Map")


'Improvement Agreement Draft
If Not Intersect(Target, Range("AC:AC")) Is Nothing Then 'Bond Estimate Approved Column
    If MsgBox("With Security Estimate Approved, Would you like to have an Agreement Drafted?", vbQuestion + vbYesNo, "Improvement Agreement Drafting") = vbYes Then
        rw = Target.Row
        Stop
        UF_AGR_Selection.Show
    End If
End If
End Sub
What am i doing wrong.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,544
Office Version
365
Platform
Windows
This line
Code:
Public rw As Long
Needs to go in a standard module.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,544
Office Version
365
Platform
Windows
You're welcome
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,685
Office Version
365
Platform
Windows
You could also create a userform property to hold the value, or even reference the target range. For example, using a userform called UserForm1:

Sample code in UserForm1:
Code:
Private m_rngTARGET         As Excel.Range


Public Property Set WksTarget(ByVal rngTarget As Excel.Range)
    Set m_rngTARGET = rngTarget
End Property


Public Property Get WksTarget() As Excel.Range
    Set WksTarget = m_rngTARGET
End Property


Private Sub CommandButton1_Click()
    MsgBox "Target row is: " & Me.WksTarget.Row
End Sub
Sample Worksheet_Change event code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    With UserForm1
        Set .WksTarget = Target
        .Show
    End With
End Sub
 

Forum statistics

Threads
1,078,461
Messages
5,340,440
Members
399,375
Latest member
alwayssunny

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top