Pass A Sheet As A Parameter To A Sub

Feb 1, 2010

From Macro1, I want to pass a reference to a sheet. In Macro 2, I want to select that sheet. Here's what i have so far but I'm getting a "subscript out of range" error

Sub Macro1()
Macro2 "Sheet1"
End Sub

Sub Macro2(sheet As String)
Worksheets(sheet).Select
End Sub

ADVERTISEMENT

Macro Sub Pass Parameter

Aug 21, 2008

I've written a function to delete the charts on a worksheet: ....

View 9 Replies View Related

VBA To Pass Command Line Parameter

Oct 17, 2008

I found the following link that got me started [url], so now I have my code for reading/analyzing comand line parameters. Here is an example of the parm I'm passing in ....

View 9 Replies View Related

Pass Combobox As Parameter To Other Functions

Mar 13, 2008

Can I pass Combobox as a parameter to other function. If yes then how? I can pass Combobox1. Name as a parameter to other function but inside that function I cannot use combobox("Comboboz1.Name"). I don't know the syntax to work on the combobox whose name is passed as a parameter.

View 2 Replies View Related

Excel 2003 :: How To Pass A Parameter In The Input Range

Mar 21, 2014

I have a combobox in a excell sheet. It is possible to pass a parameter in the input range instead of Parm!$B$1:$B$10

View 2 Replies View Related

Pass The Result To A Sub Which Takes An Array Of Dates As A Parameter

Dec 27, 2008

I delcare an array of dates and assign it with a function that returns an array of dates. Then I want to pass the result to a sub which takes an array of dates as a parameter. However, I am getting a type mismatch error on this line

View 2 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

View 3 Replies View Related

Setting Parameter For One Column And If True Then Check Line Above And Below For Another Parameter

Jul 31, 2013

I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:

Experiment
Is Steward
EU ID
Location
Data Quality
GE
Entry Order

[Code] ........

I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.

The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.

View 2 Replies View Related

Count Selected Rows In Sheet (and Use As Input Parameter)

Dec 4, 2009

I have a macro that adds a row with predefined formulas and formating. The macro is launched by clicking on a button. However, I would like to make it possible to add more than one row at a time. My plan to do this was to use the number of selected row as input to the current macro. If the user selects row 1,2,3 and 4 (or 15, 16, 17 and 18, and so on) four new rows should be added. I would just add;

View 3 Replies View Related

Pass Value Of Range From Another Sheet

Nov 5, 2006

I am using a spreadsheet with two sheets, basically, one sheet has a combo box control, you select a name from there and click on "Go". The function is supposed to go into the next sheet (Sheet2) and retrieve a value from there (in the same row as the selected name).

I am using the following code (on button click):

Dim strValue As String
Sheets("Sheet2").Select
strValue = Range("A1").Value
MsgBox (strValue)

I've simplified this, but essentially my problem is this - it isn't picking up any value at all - there is text entered in A1 so what is the problem?

View 4 Replies View Related

Pass Active Sheet Name To Label

Jan 17, 2008

Is it possible for when a sheet tab is selected to have the sheet name passed to a label caption?

View 2 Replies View Related

Change Sheet Name In Cell And Pass Through Formula

Mar 23, 2007

I can't use code for this problem as I need to give this to someone who doesn't know VBA. They will need to use it in several different reports, so I can't produce something in VBA very easily. I have three worksheets, Summary, Year1 and Year2.

I want to display either Year1 or Year2 data in the Summary sheet depending on what the user enters in a cell in the Summary sheet. For example, if the user enters "Year1" in cell E2 then I want to point to a cell in the Year1 spreadsheet.
I tried using formula: =E2 & "!B3" but this doesn't work. Is there another solution? I have attached an example.

View 6 Replies View Related

Pass Data From Userform To Excel Sheet Using File Path?

Jan 18, 2012

How to pass data from userform to excel sheet using file path?

View 4 Replies View Related

Excel 2010 :: Pass Date From Datepicker In Userform To A Cell In Sheet

Jul 30, 2012

I am using Excel 2010. I have a user form with a date picker on it. I simply want to transfer the date chosen to cell A3 on a sheet. I have two problems. When I click the date picker, it doesn't show all the days of the month. When I do pick a date, it isn't transferred to the sheet. This is the code I am using in the user form:

VB:
Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
meetdate = UserForm2.DTPicker1.Value
Sheets("MeetData").Range("A3").Value = meetdate
End Sub

View 5 Replies View Related

Parameter Query

Sep 16, 2007

Here it goes.

My spreadsheet is populated by data coming from MS Query, i'm entering a parameter value to display the desired data in my spreadsheet. My problem is, i have to close and open the file to have the parameter prompt so in that case i can enter the parameter value.

Is there anyway to call the parameter prompt so i will not open and close the file, its really time consuming...

If possible, i just want a command button that calls the parameter prompt.

View 9 Replies View Related

Using Combobox As Parameter

Sep 15, 2009

I have a function which has to contain the name of a combo and use it with some given parameters, here's my example: ...

View 8 Replies View Related

How To Set Parameter To Macro

May 26, 2012

add parameter to this macro and at the end the process to index the highest result at the end, is that possible?

Here is the macro;

Code:
Sub Macro()
Range(Selection, Selection.End(xlDown)).Select
Calculate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range

[Code]..

to run 5000 times

View 8 Replies View Related

Name Of Day/Month Parameter

Mar 2, 2009

Looking for a formula that gives the 1st - 5th day of a month, using the day name, e.g. "First Monday of March 2009" returns "2". Even better is if it could accomodate previous and following months, e.g. "First Monday of Next Month" returns "6". 3

View 9 Replies View Related

MS Query Parameter With Like %'s

Mar 20, 2009

I can run my query from within MSQuery something like

like %RESISTOR%

and it works fine.

However I cannot use %RESISTOR% on the excel sheet as a parameter.

is it possible to update a query using a parameter in this way? Maybe getting VBA to actually update the query manully.

View 9 Replies View Related

Parameter Queries In Vba

Mar 30, 2007

I want know how to pass a parameter through a cell for date in the following url

http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=10

View 9 Replies View Related

Sumif With Variable Parameter?

Mar 6, 2014

I have a a table at the top of my worksheet which is a breakdown of performance. (Table 1), i would like Table 2 to roll up performance using a =SUMIFS calculation. However number of rows in table 1 may vary.

View 2 Replies View Related

Match Function Value Parameter

Jan 8, 2009

is there anyones know what's the meaning of 1 in this formula
MATCH(1,('Data Issues'!$A2=Sheet1!$A$2:$A$68)*('Data Issues'!B2=Sheet1!$B$2:$B$68),0)

View 2 Replies View Related

Prompting For Parameter Value Using Macro?

Mar 12, 2014

Last time i got macro from this forum how to import files automatically. I am importing the data from the specific folder.In code itself we are hardcoding that file names.But in that folder i have lot of files is there any option to pass any parameter value.That means each time it will asks for filename(Prompt).Once you give file name it will automatically load the data.

[Code] .....

View 6 Replies View Related

Input Parameter To Set A Limit

Apr 26, 2008

How do I adjust the VB code below so when the user clicks the command button they are prompted to set a limit.
i.e the code only runs til it hits the limit and stop (instead of running until it there are no more x in the columns)

Row 2, Col 9 stores the column numbers (listed as 1, 2,.....100) so thought this could be used in some way.

1. how do i set up the code so a user can enter a parameter value e.g 50 (which is the limit but also includes the values in column number "50")

2. how do i adjust the code so it take the parameter value and stop when it reaches column number "51"...

View 9 Replies View Related

Send Parameter Into Macro

Oct 31, 2008

I have a macro in which I will trigger it to run automatically, under windows scheduler. Eg:

"D:DataMacro.xls".

I am expecting to pass parameter in such way, which I did for my other exe application.

"D:DataMacro.xls" parameter_1.

I want the macro to read the parameter_1 in such way.

View 9 Replies View Related

Incrementing An Alphabetic Parameter

Mar 16, 2009

I am running a macro where I pass it starting column and it processes the next 10 columns. How can I pass it "J" and have it increment K,L,M,N,O,P,...?

View 3 Replies View Related

Passing Column As Parameter

Mar 20, 2009

How can I pass a column as a parameter when I execute the sub? ex:

View 2 Replies View Related

Passing Subroutine As A Parameter

Mar 26, 2009

I want to pass the name of the routine as a parameter.

View 6 Replies View Related

Get Information About The .NumberFormat Parameter

Jan 18, 2010

explain or provide a link to info about the .NumberFormat parameter?

I am using:

View 4 Replies View Related

UDF To Report Invalid Parameter?

May 6, 2012

What is the best way for my UDF to return an error to the calling worksheet if it detects an invalid parameter?

In the past, I have usually set a breakpoint so I could check the the values and the logic. Other times, I return an invalid result, like 0 or -1.

I am working on a UDF now that is called hundreds of times. The workbook is a work in progress so I am constantly making changes to the UDF and the calling cells. Periodically, I screw up and do something that causes every call to get an error (like divide by zero).

View 3 Replies View Related

Loop Macro Goes Past While / Until Parameter

Aug 2, 2012

I am running a macro to loop through a list of codes which takes the unique code, goes to a different spreadsheet, finds related data based on that code and if it meets certain criteria, and retursn the same code if it works. Pretty much a filtering process, but I have it returning the code because I do not want to do the calculations in the Macro itself.

Here is the code:

VB:
On Error Goto Label1
Do
Windows("Byron_MonthlyPDPWellList.xlsx").Activate
Range("A" & I).Select

[Code] .....

"I" begins at 2 and counts up until it reaches Count, which is the number of total rows in the column.

However, when I step through the code, it continues to loop through the code where "I > Count".

I have tried "Until I > Count", "Until I = Count", "While I <= Count". And none of them seem to work. It gets stuck in an endless loop cycle.

View 6 Replies View Related

Choose The Correct Supplier According To The Parameter Given?

Feb 17, 2014

So for example I Have three suppliers for a given delivery depending on weight to point "A" to "B"

From To AA pallet AA semi AA fullload BB pallet BB semi BB fullload CC pallet CC semi CC fullload
A B 1 2 3 4 3 4 2 3 4
A C 2 3 4 3 4 5 3 3 4

Given weight parameters that

min max mode
0 < 4 pallet
4 =< 6 semi
6 =< 12 fullload

Now I need a a table like this

Weight From to Mode Cheapest supplier pri

View 2 Replies View Related

Web Data Connection Parameter Problem

Oct 10, 2008

I'm having problems with date parameter for a web connection. I can provide the parameter and type the parameter via message box ... but when I want to reference a cell the query errors out.

View 3 Replies View Related

Range As Parameter In Function Declaration

Aug 3, 2009

I have a formula that hides rows within a range for a sheet.

View 2 Replies View Related

Tranferring From A List To A Two Parameter Table

May 12, 2009

I need a two parameters table deriving data from a list in another sheet. The vertical column of the two parameters table will be the names of the personnel, and the horizontal one will be the dates, in weeks form. The list contains the names, flight departure, flight arrival and no. of working days spent in the foreign country. What I need to do is to tranferr the data from that list onto the table.

For eg, if john flew overseas on 20-Dec to 15-Jan, the list will show john on column A, 20-Dec in column B, 15-Jan in column C and 20 working days on column D.

This list of personnel and their data should go into the two parameters table, where it will be identified when the personnel will be overseas according to the calendar of the project. Since the vertical heading is the dates of every week, a date that corresponds to period where the personnel is overseas will reflect a number in the cell. The number will be the number of working days the personnel spent on the foreign country, therefore the max will be a 5 and minimum is a 0. I need a formula that will fill every cell within the area of the tables.

View 4 Replies View Related

Passing Active Form As A Parameter

Dec 5, 2012

I got a couple of userform and want to make sure all form are closed before opening the next one.

I'm trying to pass the active user form as a parameter to a subroutine that will ensure the form is close before opening the main menu, but I'm doing something wrong.

Code:

Private Sub CmdMainMenu_Click()
Call gotoMainMenu(FrmInventoryMain)
End Sub

Code:

Sub gotoMainMenu(acsheet As String)
Unload acsheet
Unload FrmMainMenu
FrmMainMenu.Show
End Sub

View 1 Replies View Related

Passing Form Label As Parameter

Apr 22, 2014

Within a module I have about 10 Label_Click events. Since they essentially do the same thing I want to create a another procedure that these 10 label_click events will call.

This is what the click event on Label2 looks like:

Code:
Private Sub Label2_Click()
LabelClick (frm.Label2)
End Sub

As you can tell I am trying to call LabelClick routine, which looks like this:

Code:
Private Sub LabelClick(ByRef lbl As Object)
...{my code here}
End Sub

But when I run it, it throws an error at event level saying "Type mismatch".

I also tried "Label" instead of "Object" in the signature, but I get the same error.

View 2 Replies View Related

Character Length Parameter Not Working

Jun 12, 2014

I'm trying to set my code to force the user to enter in 8 characters for an employee ID within a text box. Currently when I attempt to enter in an employee ID, my message box alert appears after the first digit. So instead of validating the user has entered in 8 characters for the employee ID, the msgbox alert appears as soon as the user enters in the first number. I cannot figure out what I am missing within my code.

Code:
Private Sub txtPERNR_Change()
If Len(Me.txtPERNR.Text) 8 Then
Me.txtPERNR.SetFocus
MsgBox ("PERNR number must be 8 characters in length")
Cancel = True
End If
End Sub

I honestly thought this validation would be easy, but I've tried it a couple of different ways and nothing is working.

View 6 Replies View Related

Filter Parameter In Excel For MS Query

Jul 25, 2008

I'm trying to create a report for a coworker to run. I want to filter the parameter in a cell (ssn), so that she can run the report. I have added a button for her to execute..once she puts in teh ssn. The spreadsheet is linked to MS Query in the background. How do I tell the Query to link to the cell in excel for filtering?

View 9 Replies View Related

Vba Calling An Access Query With Parameter

Mar 12, 2007

i have code accessing an access database running a "query" which has been defined in the database itself. i asm using ADO - no issues with this. works very well.
my problem arises when i change the query to take a parameter. this works ok when i execute in access but not with the code i am using for VBA / ADO. i have tried several techniques but none seem to work.

Dim rsReport As New ADODB.Recordset
Dim cnReport As New ADODB.Connection
Dim cmdReport As New ADODB.Command
Dim prmReport As New ADODB.Parameter
cmdReport.CommandType = adCmdStoredProc
cmdReport.CommandText = "testQuery-withParam"
With prmReport
. Name = "FullName"
.Value = "joe bloggs"
.Type = adChar
.Size = 8
.Direction = adParamOutput
End With................................

View 2 Replies View Related

Sum Across Specific Worksheets Based On Defined Parameter

Jun 11, 2014

I have a workbook with 25+ worksheets, all identical formats that each represent a different client. Each client is assigned a project team. I want to be able to sum up the worksheets by project team, but have flexiblity in doing so when I add new clients etc. So a couple of thoughts.

1. Is there a formula that I can create that compares the worksheet name to a list of names assigned to a particular client?

2. Can I reference a cell in each worksheet that identifies that sheet as a client that is to be included in the team summary?

Example of worksheet sequence

Worksheet - Summary Sheet - All Clients
Worksheet - Client 1 Team Alpha
Worksheet - Client 2 Team Beta
Worksheet - Client 3 Team Alpha
Worksheet - Client 4 Team Beta
Worksheet - Client 5 Team Alpha
Worksheet - Client 6 Team Beta

I want to create summary worksheets (identical to the individual client sheets) for each team.

View 1 Replies View Related

ADVERTISEMENT