Pass Worksheet_Change Target Value to Userform

zoog25

Active Member
Joined
Nov 21, 2011
Messages
343
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,635
Office Version
365
Platform
Windows
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
343
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
35,934
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
35,934
Office Version
365
Platform
Windows
You're welcome
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,796
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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,078
Messages
5,412,245
Members
403,423
Latest member
fori_gump

This Week's Hot Topics

Top