Pass Worksheet_Change Target Value to Userform

zoog25

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

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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:
Upvote 0
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.
 
Upvote 0
This line
Code:
Public rw As Long
Needs to go in a standard module.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top