Re: [ExcelVBA] passing variable from onclick to userform

 

Hi,

Would you like to have an excel sheet posted to the site so people can look at it in order to help you better?

$$If so... post it directly to a moderator with a request.

Lisa

Sent: Sun, Feb 26, 2012 1:48 pm
Subject: [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
tems that i want to display in a userform. I have this made in the code below
nd it works. My problem is I have several tables and I want to use the same
serform by passing variables that are in the onclick macro's for each table.
xample living room table when clicked on will pass living room var stored in
he macro while the dinning room table will pass dining room var stored in it's
acro that in turn retrieve info from sheet 2's spreadsheet (database) the vars
re the 2 (two) autofilter criterial in the code below, which are MR0008 and T1.
he floor plan is just an onclick macro that directs each item to a different
serform. but i want just one userform with the 2 vars identifing that table's
acro.
have this working now but I have to copy the one userform code to each table's
serform. then i hand code each autofilter criterial in that tables userform to
atch that tables database info search.

rivate Sub UserForm_Initialize()
Author : Jim Neely
Macro Purpose: To populate a listbox with data from
a worksheet range
pplication.ScreenUpdating = False
heets("Gages").Select
ange("A2").Select
election.AutoFilter
ctiveSheet.Range("$A$1:$Q$905").AutoFilter Field:=9, Criteria1:="MR0008"
ctiveSheet.Range("$A$1:$Q$905").AutoFilter Field:=10, Criteria1:="T1"
ange("A1:Q905").Select
election.Copy
heets("Sheet3").Select
ange("A1").Select
ctiveSheet.Paste
ange("A2").Select
heets("Gages").Select
ange("A78").Select
pplication.CutCopyMode = False
election.AutoFilter
ange("A2").Select
Windows("ScratchWB.xlsx").Activate
heets("Sheet3").Select
im lbtarget As msforms.ListBox
im rngSource As Range
Set reference to the range of data to be filled
et rngSource = Worksheets("sheet3").Range("A1:Q500")
Fill the listbox
et lbtarget = Me.ListBox3
ith 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
nd With
Dim savechanges As Boolean
savechanges = False
Application.ActiveWindow.Close (savechanges)
heets("Map").Select
pplication.ScreenUpdating = True

nd Sub

[Non-text portions of this message have been removed]

__._,_.___

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

Messages in this topic (2)

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

.

__,_._,___