[ExcelVBA] passing variable from onclick to userform

 

I have a made a floor plan in excel that i have a table and on that table are items that i want to display in a userform. I have this made in the code below and it works. My problem is I have several tables and I want to use the same userform by passing variables that are in the onclick macro's for each table. example living room table when clicked on will pass living room var stored in the macro while the dinning room table will pass dining room var stored in it's macro that in turn retrieve info from sheet 2's spreadsheet (database) the vars are the 2 (two) autofilter criterial in the code below, which are MR0008 and T1. the floor plan is just an onclick macro that directs each item to a different userform. but i want just one userform with the 2 vars identifing that table's macro.
I have this working now but I have to copy the one userform code to each table's userform. then i hand code each autofilter criterial in that tables userform to match that tables database info search.

Private Sub UserForm_Initialize()
'Author : Jim Neely
'Macro Purpose: To populate a listbox with data from
' a worksheet range
Application.ScreenUpdating = False
Sheets("Gages").Select
Range("A2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$Q$905").AutoFilter Field:=9, Criteria1:="MR0008"
ActiveSheet.Range("$A$1:$Q$905").AutoFilter Field:=10, Criteria1:="T1"
Range("A1:Q905").Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Range("A2").Select
Sheets("Gages").Select
Range("A78").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A2").Select
'Windows("ScratchWB.xlsx").Activate
Sheets("Sheet3").Select
Dim lbtarget As msforms.ListBox
Dim rngSource As Range
'Set reference to the range of data to be filled
Set rngSource = Worksheets("sheet3").Range("A1:Q500")
'Fill the listbox
Set lbtarget = Me.ListBox3
With lbtarget
'Determine number of columns
.ColumnCount = 17
'Set column widths
.ColumnWidths = "100;80;50;50;50;50;50;100;60;50;50;50;50;50;05;05;50"
'Insert the range of data supplied
.List = rngSource.Cells.Value
End With
'Dim savechanges As Boolean
'savechanges = False
'Application.ActiveWindow.Close (savechanges)
Sheets("Map").Select
Application.ScreenUpdating = True

End Sub

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (1)

Recent Activity:

• New Members 2

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___