ParamArray Option

Nov 28, 2008

My purpose is:

1) I have a macro workbook with multiple procedures (6 main tasks)

2) Some workbooks will use all procedures, and some will not use all procedures (most use either 4 or 6 of the procedures)

3) To hide the unwanted procedures as required, the menus will not appear for those choices (in code that creates custom toolbars on activate)

Solution (proposed):

1) send a paramarray argument with string elements that identify menus to use

2) send a ďshow allĒ true or false, to simplify identification of workbooks that use all procedures

Reason solution is chosen:

1) I can basically have one macro workbook this way Ė so simple implementation even where some workbooks wonít need all the macros (and should definitely not be making them menu options).

2) If I add more routines, I can just extend the ParamArray as needed and continue to identify specific toolbar options to use or not use.


1) That paramarray is interesting! It canít be passed and its initialized as an array with bounds 0 to -1. Also, it canít be tested directly to see if its Empty. So I came up with checking for the -1 UBound, combined with loading the elements into another array (seems stilted to me).

Iím not sure what Iím asking, butÖI need to determine if the paramarray is being used, and be able to pass it. Is there a simple way to do that? As Iím sitting here writing Iím realizing I could just pass a regular array and dispense with the ParamArray option -- well, Iíll post my thoughts anyway. Sorry this is so ramblingÖ

Hereís the code Iím using (basically). Note that DoSomething actually checks each element of the array to see if it matches a potential menu item (by name), and if so, marks that menu item to be added - since there's 6 menu options, I placed it in a separate method to avoid have it in the routine 6 times.

Maybe I'm just curious if anyone ever successfully uses the ParamArray option - it seems to me to be somewhat of a bother that you can't easily tell if it's been passed in or not, or use it like a regular array (check if its empty, etc.).

Sub MyToolbar(ByVal blnShowAll, ParamArray args() As Variant)
Dim a() As Variant
Dim x As Variant

View 9 Replies


Pass String To ParamArray?

Jul 3, 2014

I am trying to pass a string to a function requiring a ParamArray. See below

Declare Function LibFunc1 Lib "FuncAddin" (ByVal vtSheetName, ParamArray Mylist() As Variant) As Long
Sub testsub()
Year = 2013
Period = December
MyStr = "Year#" & MyYear & """", "Period#" & MyPeriod & """"

LibFunc1("Sheet1", MyStr) 'how to declare string so it can be passed as ParaArray?

End Sub

View 1 Replies View Related

Paramarray To Write Records To Access

Dec 21, 2009

I'm trying to simplify things a bit and create one sub with the appropriate parameters. My experience is limited with arrays so I thought I'd get snagged right where I did. I want to pass a paramarray, which contains two "columns", the access table field names and matching columns from the excel worksheet. This way, to add records to a table, I can simply pass the table name, source worksheet, and paramarray that provides the field/ column info. I'm getting the error "Wrong number of dimensions."

Sub test()
Dim testArray(0 To 4, 0 To 1) As Variant
'Write the field names and corresponding columns to an array. The first array "column represents the access
'table field names, the second is the corresponding column from the excel worksheet.
testArray(0, 0) = "Test1"
testArray(0, 1) = 1................

View 9 Replies View Related

Excel 2013 :: CF With 2 Option Buttons / Multiple Option Groups?

Sep 6, 2013

I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).

Excel 2013

View 1 Replies View Related

Option Group Box

Jan 24, 2007

If you have 5 option boxes in the group at the top of a form and want to make sure the user of the form selects at least one box before continuing on, how do you do that?

View 9 Replies View Related

Multi Option Sum

Jan 8, 2010

I want to sum data in COLUMN C that is related to COLUMN B if the COLUMN F is blank.

View 10 Replies View Related

Adding A New Option?

Feb 16, 2014

I have the following formula =IF((F3=1)*(H3=1)*AND(J3=1),1,0) and I do need to add the option C1 besides 1 so it would be something like this...


View 7 Replies View Related

Right Click Option

Jun 7, 2009

I lost my right click option in excel, all I get is a little gray box that does nothing. I know it is something I did today, it has worked perfect before. Has anyone seen this happen, the problem is only in excell 2003.

View 2 Replies View Related

Set An Option Button By Name

Oct 8, 2009

In Excel 2003 VBA, what is the syntax for controling an Option Button or Checkbox by name or caption

I have tried the following but has an error, I am sure this is a simple one.

View 4 Replies View Related

VBA / Msgbox With No Yes And No Option

Jul 21, 2012

I am trying to have a msgbox come up if there is a path found it will pop up a msgbox for a few seconds then go away. Im trying to avoid the user from having to select anything just simpley telling them the connection is there then go away on its on. The code below is what Im currently using.

If Dir("H:") = "" Then
MsgBox "Error: Drive, path or file not found"
MsgBox "OK: Drive, path or file found"
End If

View 9 Replies View Related

Option Buttons

Aug 23, 2007

I created a frame in a spreadsheet I put in 2 options buttons that I called Left the first one and Right the second one. I got 2 cells lets say C10 and G10 in these 2 cells I write the cell number values. When I click on button one or Left, I need the cell C10 be set focussed and write a number e.j 5. However, when click on the second butt to write a number e.j 7, the number in the cell C10 still there, so, what I need is a cero or an empty cell C10, and viceversa. Could you help to write a code for this problem.

View 9 Replies View Related

What Does Option Explicit Actually Do

Aug 30, 2008

i've used this a few times at the top of various modules, as suggested by various knowledgable people on here, but it might be a good idea to find out what it means, and what it actually does!

View 9 Replies View Related

How Do I Make A Msg Box With Out Any Option

Dec 29, 2009

I made a msg box but i dont want any option to be displayed in it.. it only should display as an info to the user.

View 9 Replies View Related

Option Explicit ....

Feb 18, 2007

I have some VB code "For, next" that I have written with numerous variables. All the data appears to be totaling correctly. After reading some comments on this site I added Option Explicit to the top. Well, it stops on my first For Each Cell, with cell highlighted and an error message 'variable not defined'. I have searched and searched and can't find what it might be. Does this error message mean I am getting incorrect results? Is there an easy way to determine which variable is not assigned? A function key or something?

View 9 Replies View Related

Two Option InputBox

Mar 22, 2007

I am trying to simply the data input for someone who is not very experienced.

I have had some success but need to have an extra option in case of mistakes.

Ideally I need the following -

I want an inputbox that will appear when the user starts the macro and for example asks Enter Surname

Then I know what I want but I don't know how to do it.

I want a box to display the Surname that the user typed into the inputbox with a message asking Are Details Correct? with a Yes and No Button.

Clicking the Yes button moves onto the next inputbox clicking the No button cancels the whole macro before it enters data into cells.

View 9 Replies View Related

Option Explicit

May 3, 2007

I work on a worksheet that has 10,000 rows all in column A with members that have 353 different plans.

Here is an example:

This is for plan 426-0

This is the total for plan 426-7000

I need to move the plan type for each member to column c. When moving the plan type I only want the number have the dash. For Robert/James/Ted in column C the plan type is 0. For Amanda and Nikki in column C the plan type is 7000. I have created a macro that can move the plan over but not the number after the dash.

Here is what I have come up with so far.

Option Explicit
Option Compare Text

Sub R007()
Dim i As Long
Dim sPlan As String

For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Left(Cells(i, 1), 5) = "Plan " Then
sPlan = Cells(i, 1)
Cells(i, 3) = sPlan
End If
End Sub

View 4 Replies View Related

Set All Option Buttons To No

Jun 26, 2007

is there a way to set all yes/no option butons to "no" when a worksheet is activated?

View 5 Replies View Related

Adding A Second Option To COUNTIFS?

Dec 26, 2013

I have a COUNTIFS function that, among other criteria, only counts cells in which the value is "P". I now want to modify this function to count cells in which the value is "LA" in addition to cells in which the value is "P". I'm not sure how to work this into the argument. I tried using OR("P","LA"), but that's not working.

View 3 Replies View Related

Option Button Grouping

Dec 10, 2013

I'm trying to replace a bunch of checkboxes on a sheet with a bunch of Form Control Option Buttons. The problem I'm having is every option button I add seems to be grouped with all of the rest of my option buttons. If I add buttons 1 & 2, I need those 2 grouped together, and when I add 3 & 4, I need those grouped together. Currently it's grouping all 4 buttons together so 1 - 4 are linked, but I need 1 & 2 linked together, and 3 & 4 linked together. I've tried grouping, but it still doesn't function how it ought to.

View 13 Replies View Related

How To Unload Option Buttons

Jan 17, 2014

Attached I have kind of a score card, when you open the tab named TEAM1 a userform pops up and I pick the partners that played together, then I can pick an option button labeled 0,1,2 or 3, when it unloads, is it possible to make it put the corresponding number by their names in the empty row (W) of the week they are playing?, and not just the 1st open row available?

For example, if I pick the names Ryan and Stu the number will go under week 10 (X3) and not find the empty cell in (J3)?, I have no clue how to make this happen, but I made a userform to use, to try and make it work.


View 8 Replies View Related

ActiveX Option Buttons VBA

Mar 19, 2014

I am looking for a way to use 2 optionbuttons in vba. The option buttons are in different groups, and are independent.

I cannot get this to work. Is this even possible? In sheet1, the vba would read something like this.

View 9 Replies View Related

Option Button To Post Value

Mar 18, 2009

Not sure how too do this, here is a simple example of what I want too do. Just using different buttons to post a number.

View 6 Replies View Related

Option Buttons And Combo Box

Jun 2, 2009

I use Excel2007 and saved this workbook as excel2003, the combo box doesn't dropdown anymore? I don't understand it just settings? If you could have a look at the code I use for the option buttons...when I select the first optionbutton it calls the msgbox without selecting the offset cells. From optionbutton2 onwards it works fine.

View 4 Replies View Related

Option Button Usage

Jan 2, 2010

I have a worksheet where the user selects between two option controls, which are mutually exclusive. when they toggle an option, a user form pops up, depending on which option they selected, and askes them for a number, which is used in a calculation elsewhere in the page. The user form has a cancel button, in case they decide to back out. If they cancel, I want the option buttons to reset back to their original state.

The problem I'm having is that when I code it, if you select cancel on the user form, it causes the option to revert back to it's previous state, but that triggers the selection of the option again, which pops up the form, making a neverending loop! Is there a better way to do this? I tried it with a toggle button, with the same results.

View 14 Replies View Related

Option Buttons To Use For Searching

Jan 13, 2010

I know that there is an ocean of information out there with regards to option buttons, but for the life of me I am unable to find what it is I am looking for. So I will try to explain what I am trying to do as best as possible. In my workbook I have 4 sheets. Sheet 1 contains all the data. The remaining sheets have taken all the data and broken it down by machine type. IE(carton errector, Carrier errector, etc) I have a series of user forms that you would navigate through to perform a search. For This example frmMain_menu is where the user would start out. I only have code added for the first button (Pearson).

This will bring up another form with more options for the user to select. Currently I only have code for the Carton Errector button. Once the user has selected that button frmCarton_Errector pops up and it has a series of 12 option buttons to choose from. What I am trying to accomplish is say the user selects cylinders, I would like once the search button is clicked for the program to is search sheet2 col f for the caption value of the option button selected. Any hits that it might find I would like to display certain datafrom the rows in a list box.(IE col A,B,G)

View 4 Replies View Related

Keep Text Only Option For Paste

Mar 22, 2012

For some reason I only have the option of "keep text only (T)" in the paste options and cannot copy and paste formulas. Can you tell me how to change this and why this happened?

View 5 Replies View Related

Option Button Group Value

May 14, 2013

I have user form with 5 OptionButtons in one group called Group1 and another 5 in Group2.

I need that those 5 OB in each group had value from 1 to 5 and then user selects one OB it will inset that value (not TRUE or FALSE) into cell and same with Group2.

Private Sub CommandButton1_Click()
Cells(ActiveCell.Row, "E").Value = Need value of Group1
Cells(ActiveCell.Row, "D").Value = Need value of Group2
End Sub

View 5 Replies View Related

Multiple IF With Option Button?

Sep 18, 2013

I am looking for Multiple IF formula that linked with options button.

If D10=True 16+(C12*2.10)
If D11=True 14+(C12*1.6)
If D12=True 12+(C12*1.3)
If D13=True 10+(C12*1.15)
If D14=True 16+(C12*2.10)
From D10 to D14, there is always one True and others are false.

View 2 Replies View Related

Option Button Reordering Itself

Nov 7, 2013

Working on a project for a client, and I have two option buttons set up on the spreadsheet, grouped together with a frame and linked to the same cell. Working on the sheet on my computer, the button on the left gives me a value of "1" and the button on the right is "2". When I email the spreadsheet to my client, the buttons reorder themselves, so that the button on the left gives him a value of "2" and the button on the right is "1". Is there a way to prevent this?

View 1 Replies View Related

Login Option On Workbook

May 1, 2007

if it's possible to add a login when you open a workbook and keep a log of who has logged in with the time noted......or am I being a bit optimistic?

View 9 Replies View Related

Copyrights 2005-15, All rights reserved