PDA

View Full Version : creating Parameters in Excel using VBA



nockvb
03-29-2014, 03:51 PM
It would be great if someone can show me a great example i can follow that would help me create a parameter in Excel using VBA that would be able to change table values in another sheet in the same file.

Thanks.

SamT
03-29-2014, 04:48 PM
Not enough information.

The only parameter that I know of in VBA is the value variable(s) used to pass information into a sub or function.

In the below code "rng" inside the parentheses after the Test" is a parameter.

Sub test (rng As Range)
If rng.value >100 Then rng.Interior,ColorIndex = 3
End Sub

nockvb
03-29-2014, 09:48 PM
Thanks for the response "SamT".

Here is the scenario that I'm hoping to resolve:

If I have a data-table value that has for example this value:
Table name = Employees
Column Names = Names, Titles
The values of the the columns will consist of
"SamT, Manager"
"Nock, Manager"
"John, Entry Level"
"Kris, HR"......

So If I have this table generated from a sql server table on Sheet One on my excel spreed sheet, How can I create a Parameter that will help me filer this table automatically by entering values. For example I want to add "Manager" in the spreed sheet that I will have my parameters set and on the next spreed sheet with the "Employees" table list only shows the employees with the Manager Title.

Hope that helps explain my initial question.

Thanks for your time to help me out. I appreciate it.

GTO
03-30-2014, 12:37 AM
Greetings,

Sam may decipher that, but in the meantime, could I suggest uploading a small example workbook? Either existing, or same data types for each column, and explain what all the user is supposed to be able to do. "It looks like this (sheet1) when imported, and I'd like it to look like this (sheet2)..." examples seem to be the easiest for most 'helpers' (including yours truly here).

Hope that helps,

Mark

SamT
03-30-2014, 06:54 AM
@ Mark, Sorry, no can do.

@ NockVB, Try using the Autofilter option of the Filters command on the Data Menu.
then click the down triangle next to the "Titles" column label. Select "managers" in the drop down list.

GTO
03-30-2014, 11:59 AM
@ Mark, Sorry, no can do...


Hi Sam,

I meant maybe the OP could upload the workbook :p

Mark

Paul_Hossler
03-30-2014, 02:05 PM
http://www.vbaexpress.com/forum/images/misc/quote_icon.png Originally Posted by SamT http://www.vbaexpress.com/forum/images/buttons/viewpost-right.png (http://www.vbaexpress.com/forum/showthread.php?p=307448#post307448) @ Mark, Sorry, no can do...
Hi Sam,

I meant maybe the OP could upload the workbook :p

Mark



I just figured SamT could not decipher it either -- At least I was in good company


@nockvb -- I think a small workbook with a 'before' and 'after' is the best (only) way you'll get a suggestion you can use

Paul

SamT
03-30-2014, 02:17 PM
Hi, Mark.

I meant I couldn't decode it either :D

nockvb
03-30-2014, 03:47 PM
Ok Here is the example:

Sheet one (Parameters) is the page where I will have the list of the Titles in a drop down. The user will select a title of their choice and during that time on Sheet two(Table Data) that list gets filtered according to what was selected from the list.

I have tried to use the Data validation in excel to do it but I had a hard time making it work with the source link not being recognized so I figured there has to be a way to do it in VBA.

Aussiebear
03-30-2014, 05:40 PM
Make an employees table on another sheet called say "Data". Then you can use vlookups to fill in data on the sheet you are working on.

Or you can make up dependant lists as the alternative to this option

nockvb
03-31-2014, 11:01 AM
I'm actually trying to filter an existing table values not necessarily insert or modify a table.

SamT
03-31-2014, 11:04 AM
@ NockVB, Try using the Autofilter option of the Filters command on the Data Menu.
then click the down triangle next to the "Titles" column label. Select "managers" in the drop down list.

Bob Phillips
04-02-2014, 02:16 AM
Nock,

Take a look at Advanced Filter, you can setup a small table of criteria and then filter your table using those criteria.

snb
04-02-2014, 03:21 AM
see the attachment