Use Input Box Variable In More Than One Module / Procedure?

Apr 10, 2013

i know i have done this before but im totally blanking. I want to user the inputvalue in procedures called from controller, but it just shows as empty.

Code:

Sub controler()
Static inputvalue As Long
inputvalue = InputBox("Please set min size of trades.")
'other macro's called here
end sub

View 4 Replies


ADVERTISEMENT

Exit Module Not Procedure

Jan 11, 2007

I know to exit a sub one uses 'Exit Sub', however I have a sub that is calling a second sub (within the same model), and if a variable is a certain value I want to exit...however, when I use 'Exit Sub' it exits the called sub, but then just continues from the original sub...it's not exiting it too. I can work a work around, but I'm wondering if there is a built in solution to this problem?

View 3 Replies View Related

Use An Array From One Procedure As Input For Second Precedure

Feb 28, 2010

There's probably a post for this, I just can't find it.

I need help "reading" an array from a previously used procedure. The second procedure is dependent on the values from the first, however, I can't seem to "import/read" the array from my first procedure.

I know the code is executing correctly in the first procedure, i can output it to a worksheet. The output data isn't of any particular interest, it would be "taking up space" on my worksheet, but the values in the array are important for latter caclulations. I would prefer the array be "hidden".

Ex.

public delta( 0 to 360) as double

sub first()

code...
delta(theta) = x0 - x
worksheets("output").cells(theta, 1).value = delta(theta) 'Just to make sure the code is working

end sub

sub second()

?What code goes here to read the previous array delta()

end sub

View 9 Replies View Related

Use A Variable To Run Or Call A Procedure By Name

Sep 8, 2007

I have 46 drop downs that I need to call from a main sub. I want to create one loop that will call each one to perform its function. Below is a simple example of what I am trying to do, but where I need help is with the 'Call Y' line. I am not sure what character to use to tell excel to look at what is in the value of Y, not 'Y' itself

Sub testitout()
Dim y As String

For X = 1 To 2
Sheets(" Lookup"). Range("E3").Value = X + 4
y = "dropdown" & X & "_change()" 'this will return "dropdown1_change()" for the first loop
Call y 'i want to call dropdown1_change() below
Next

View 9 Replies View Related

Return Variable From Other Procedure

Jan 19, 2008

I've search here for a bit and could not find the answer to what I'm looking for. I have 2 procedures on the same worksheet. I need to get the value of a variable from a second procedure so that my first procedure can work with it. From the first procedure I call the second, where the value of the variable is calculated, but when I return to the first procedure, the value of the variable seems to get lost.

View 6 Replies View Related

Keep Variable Values AFTER Procedure Ends

Mar 19, 2007

I have a User Form with 2 groups of 3 radio buttons let's say. The user must choose one button from each group, then press "GO" which runs specific code depending on what buttons they pressed.

How do I make the variables public or static so that after both radio buttons were selected, another seperate procedure in the same module (The GO procedure) can know what selections were made so it can run code based on those selections? In this example, there are only 2 groups of three radio buttons, but in reality I have 30 buttons and it keeps growing so I need the most direct/simple way to solve this.

For Example:

UserForm1
Radio Button Group 1:

Button 1
Button 2
Button 3

Same UserForm1
but now here is Radio Button Group 2:

Button 4
Button 5
Button 6

I need to know which buttons the user clicked from Group 1 and Group 2 after they finished both their selections from each group, and then run code depending on what combination of buttons have been selected. I'm good to go with the If/Then code to decide which code to run, I just cant keep the variables from reading as 'Empty' when I try to see what the user selected when pressing GO and running that procedure.

View 9 Replies View Related

Getting Variable Value From Another Sub/Module

Sep 30, 2006

I am writing a Sub2, where I need to extract the value of another variable, which is calculated in some other Sub1. I declared the first Sub1 (where the variable is being calculated) as Public Sub1,

View 3 Replies View Related

Getting Variable From Module To Userform

Feb 21, 2014

I got some codes defined as below and I want use a userform for and checking the process. If ı could use variable "i " in userform, my codes would work.

Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.

Set KeyCells = Range("E3:P36")

[Code] ......

View 2 Replies View Related

Loop The Procedure With The Variable Number Included In The Checkbox Name

Nov 21, 2006

I'm creating a user form that will have 10 checkboxes on it. Depending upon certain conditions being met elsewhere in the workbook, I would like to populate the checkboxes' captions with data from the workbook.

My question is, can I loop the procedure with the variable number included in the checkbox name (well, more to the point, HOW can I loop the procedure...)?

I would like to do something like this:

Dim a As Integer

For a = 1 To 10

With Worksheets("Hi-Tech")

If .Cells(a + 1, 2).Value "" Then
chkHiTech & a.Enabled = True
chkHiTech & a.Caption = .Cells(a+1, 2).Value
lblHiTech & a.Enabled = True
lblHiTech & a.Caption = .Cells(a+1, 2).Value
etc.

View 6 Replies View Related

Passing A Private Variable To Another Module

Nov 25, 2008

I have some code in which I need to pass the value of "j" from one module to another. "j" is declared in Sheet1 (Data) under the Microsoft Excel Objects.

View 8 Replies View Related

Passing Variable Value From Private Sub To Module

Aug 12, 2009

I am trying to pass a variable called "Filter" from a Private Sub to a Module but keeping coming up with a zero value in the Module. I tried to make the variable Global but that didn't seem to work.

Private Sub Code as follows:

View 3 Replies View Related

Transfer Userform Variable To Module?

Jun 12, 2013

i have a userform where when i initialize i load my combo box with a range

I have a variable which = combo box value

Sub cmd click()
myval = cbo.value
unload me
End sub

How can i use or pass this variable to my standard module i.e

Sub test()
myevent = myval
end sub

View 6 Replies View Related

VBA / How To Use Variable In Userform Declared In Module

Sep 9, 2013

I am trying to use some code similar to what i have used in one of my Modules in a Userform (The module calls this userform)

I have declared a few variables in the module:

Code:
Public LastRow As Long
Public TabLen() As Integer
Public AHUArray() As String
Public ArrayDim As Integer

and i want to try and use them in a userform (i call the userform through an bit of code that usures that the other subs that assign values to the variables have been used so there should be values in the variables??i think?)

i get to this line in my userform

Code:
TabPos = TabPos + TabLen(i)

this is the first line that requires a value to be in the array and it errors: Subscript out of range

I just want a few variable that are used in a module to be available in other modules and userforms.

EDIT:the (i) is decalred in this sub within the userform. A thought occurs, the sub in the userform is a private sub, might that effect it?Also, explain Sub vs Private Sub vs Public Sub?, im new to this! the sub that fils the arrays in the module is just Sub SubName_Click()

View 4 Replies View Related

Save A Module Level Variable

Jul 20, 2006

I would like to save a module level variable but do not know how to do it. I have tried using the public key word. If I put it in the sub, I get an error when it tries to compile the sub. If I put it outside of a sub, it just doesn't work. Surely this is something easy.

View 8 Replies View Related

Module Variable Not Recognized In Userform

Oct 16, 2006

I created in module custom toolbar with a button, see the

Option Explicit
Public ctrlGUI As CommandBarControl
Sub CreateToolbar()
Dim cb As CommandBar
On Error Resume Next
Application.CommandBars("Cust").Delete
On Error Goto 0
Set cb = Application.CommandBars.Add( Name:="Cust", temporary:=True)
With cb
.Visible = True
Set ctrlGUI = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrlGUI
.FaceId = 3205
.Style = msoButtonIcon .............

View 5 Replies View Related

Pass Variable To UserForm Module

Dec 28, 2006

I have a macro that when it runs presents a form that gives the user an option to skip the calculations or continue. If they click Skip then it continues the 'Next CE' . My problem is I can't get the Boolean value to pass from the form to the macro. Here is what I have

The main macro

Sub Dividend_MF()
For Each sh In ActiveWorkbook.Worksheets( Array("G&I", "Growth"))
For Each ce In sh. Range("a5:a" & sh.Range("a65536").End(xlUp).Row)
If Not IsEmpty(ce) And Not (IsError(ce.Offset(0, 56))) Then
If ce.Offset(0, 56) = "X" Then

View 9 Replies View Related

Shell Function Compile Error - Expected Procedure Not Variable

Aug 8, 2014

I have a VBA shell script that worked just fine on another computer, but now it won't run.

The code that won't execute is:

[Code] ........

View 12 Replies View Related

Call/Run Macro Within Same Module Using Common Variable

Feb 3, 2009

The attached file contains a simplified version of a more complex macro in which a sub routine will be called a number of times to change the colours of cells in different ranges. However, an error is triggered because the a variable and range are not defined in the subroutine although they are in the main part of the macro.

I know I am missing something obvious here, but I'd appreciate any help in knowing how I can define the ranges in the main procedure and then call the subroutine to change values in the different ranges.

View 6 Replies View Related

Get A Module Wide Variable To Provide The Current Filename To The Various Subs

Mar 10, 2009

I am trying to get a module wide variable to provide the current filename to the various subs.

View 2 Replies View Related

Variable Sheets, Variable Input

Mar 23, 2009

I run a machine that scans parts and is able to output the scans into Excel. Each part scanned creates a new sheet, and the number of sheets is variable as the number of parts scanned depends on the size of the lot. Also, since each part scanned is going to have a different number of features, the information being output on each sheet will be variable as well.

I am trying to figure out how to write a macro that will find the first feature, find the values for that feature I am looking for, output a max and min into a final sheet, and repeat for each feature, and for each sheet. I hope this is clear.

View 3 Replies View Related

Keeping Track Of Which Macro Is In Which Module Since You Can't Rename The Module?

Mar 27, 2009

After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?

View 2 Replies View Related

Matching Up Fields With Variable Input

Oct 12, 2009

Example:
Order Number from VendorOrder Number from SupplierTracking Number from Supplier12345123451ZABCD13456134561ZBCDE134561ZCDEF14567145671ZDEFG1456714567

In these three orders, the first order nubmer has a 1-to-1 relationship with a single tracking number. In the second, there are two different tracking numbers associated with one number from column A. In the third, there is only one tracking number but three different lines of the same order number that need to be updated with it.

The question is, is there a way to line up data fields head to head even when there are variable inputs like this?

View 15 Replies View Related

Filter By Variable Captured From Input Box

Jul 17, 2007

I have a Workbook containing a list of records. I'd like to create some code to

1) Get the user to input their initials when the workbook opens, and capture the response
2) Clear any filters currently applied
3) Apply filters to a non-continous range of columns
4) Apply the input response as the criteria to one of the columns, and 'blanks' to another 2 columns

Private Sub Workbook_Open()
Static varUser As String
'sets varUser variable as String & stores it
varUser = InputBox(Prompt:="Enter Intials (leave blank to view all)", Title:="Login")
'Activates input box to capture value
Dim ContractsSheet As Range
Set ContractsSheet = Worksheets("Contracts").Range("A1:AA999")
ContractsSheet.EntireRow.Hidden = False....................

View 4 Replies View Related

Copy Codes Of Module 1 And Transfer To Module 2?

Jan 24, 2013

Let's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?

View 1 Replies View Related

Making Msgbox In One Module With (Yes) Linking To Different Module

Jan 19, 2012

i got a question how i can make a msgbox in one module with 'Yes' linking to a different module.

such as: this is located in module2

Code:
If MsgBox("Do you want to activate module1?" & vbCr & _
"" _
, vbYesNo, "Choose") = vbNo Then Exit Sub

i want that vbYesNo different

No as in Exit Sub

yes as in activate Module1

View 5 Replies View Related

Input Daily Data - Variable Do Not Move In Formula

Jun 6, 2014

I have create an excel formula that I need to input data daily.

Every day, I need to insert a line and input my new data.

Below is my formula

=IF(V159="Skip","Go",IF(P160>0,IF(COUNTIF(C60:D154,">"&H159+3),"Good"),IF(COUNTIF(C60:D154,"<"&H159-3),"Good")))

The problem is that when I insert a line. Most of my variable will change according except C60:D154

As you can see below

V159 change to V160 = This is good

C60:D154 do not change accordingly. How can I make it change accordingly ?? Why it is not changing when I insert a line ?

=IF(V160="Skip","Go",IF(P161>0,IF(COUNTIF(C60:D154,">"&H160+3),"Good"),IF(COUNTIF(C60:D154,"<"&H160-3),"Good")))

View 7 Replies View Related

Series Fill Based On Variable Input Data?

Mar 21, 2014

I have a spreadsheet where the start date is manually entered in Cell B1 and an end date is calculated in Cell B3. I would like to have the dates between the start and end date, with a step value of 7 days show in the rows under Cell B3. How do I get this to work?

View 3 Replies View Related

Random Option Selection Based On Variable Input

Jul 23, 2009

I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.

It is probably easier to look at the attached sheet and the previous discussion to see what I mean.

Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html

View 2 Replies View Related

Custom Function With Variable Number Of Input Parameters?

May 10, 2013

how to create a custom function with an unknown/changing number of input parameters?

Specifically, I need to create a function that will calculate the average of 1,2 or more ranges. I can't use the built-in average function as I then need to use the average in my function.

what I have so far is:

function points(current as range, previous as range)
if previous > current then final=1
if previous < current then final=0
if previous = current then final=2
points=final
end function

This works okay if I only only have 1 range of values. but in my worksheet I need to be able to enter =points(a1,c1:f1,g1) and have the function calculate the average of c1:f1,g1 and compare it to a1.

View 6 Replies View Related

Return Cell Data From List When Variable Input In Second Worksheet

Nov 25, 2013

I want to return the data from a list in a cell on worksheet1 if I input a variable on worksheet2. I need to avoid VBA and pivot tables.

Example, if I put "Expense" in the input cell on worksheet2, I want to return a list of each row that has "Expense" in it on worksheet1:

Worksheet 1 (ColumnA/ColumnB):

Revenue/45,000
Expense/20,000
Asset/43,000
Liability/21,000
Revenue/6,000
Expense/9,000
Expense/11,000
Liability/13,000

Worksheet 2 required output (no row gaps or spaces):

Input cell "Expense" - in A1

List required (A3:B5):

Expense/20,000
Expense/9,000
Expense/11,000

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved