Pass Worksheet_Change Target Value to Userform

[zoog25]

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.

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]

How about declaring a public variable (as long) then setting that to the row number during the w/s_change event...

Dim rw as long
rw = Target.Row

...then calling rw when you need it?

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

 

[zoog25]

Thanks for the advice. I'll try it but i feel it will work.

 

[zoog25]

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.

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]

This line

Public rw As Long

Needs to go in a standard module.

 

[zoog25]

Thanks Fluff. That worked.

 

[Fluff]

You're welcome

 

[Jon von der Heyden]

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:

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:

Private Sub Worksheet_Change(ByVal Target As Range)
    With UserForm1
        Set .WksTarget = Target
        .Show
    End With
End Sub