Passing A Variable On OnChange-event

Aug 8, 2006

I've been trying to pass a variable from the OnChange- event on one sheet to a macro located in module one, but it seems I cant (or havent figured out how anyway). Anyone knows how to do it? The code here give me the error "RefreshSetup(WhatSheet) cannot be found"

Alternativly, maybe someone can come up with a better solution on my entire problem. 3 sheets with webquery. All should be handled in the same way (copy-pasting only), but source and destination-sheet differs. My big problem is if two queries are done at the same time, they conflict with eachother, and data from one query is copied on to the wrong sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then
RunWhen = Now + TimeSerial(0, 0, 1)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
Else
RunWhen = Now + TimeSerial(0, 0, 3)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
End If
End Sub

Module1:................

View 2 Replies


ADVERTISEMENT

Disabling End Statement During OnChange Event

Mar 25, 2009

I am writing an XLA in XL2002 which will act on a number of legacy and new spreadsheets. During the execution of the XLA I set cell values.

i.e. m_rRange.Cells(j, i).Value = m_sInVal(j, matchIndex)

This then triggers the onChange event of the worksheet. Unfortunately, for one of the spreadsheets that this needs to work with, this causes an error. The error is trapped within the spreadsheet itself but the sub End is called which halts all VBA code execution.

My problem is that the XLA has not finished working and gets terminated early. I cannot capture this using an onError statement as it is never triggered (the spreadsheet code stops the execution and control never returns to the XLA).

I can disable the events using Application.EnableEvents but this means that none of the onChange events are called which is also not desirable.

So my question is: is there any way of disabling the termination of code using the End() statement, or of detecting when it happens to stop it?

View 9 Replies View Related

Workbook_BeforeClose Event - Passing Arguments

Oct 8, 2008

I'm trying to use Workbook_BeforeClose event to delete my custom menus.

However, when I try to pass the custom menu caption to the Sub, I get a compile error (Expected Function or Variable).

I believe this has something to do with global variables declaration or something...

View 10 Replies View Related

Passing Variable To Two Macros?

Jul 25, 2012

I have the following code in 'ThisWorkbook':

VB:
Private Sub Workbook_Open()
Call Meetdata
End Sub

It calls this macro which is in a standard module:

VB:
Public firstvariablename As String
Public secondvariablename As String
Sub Meetdata()
regionname = InputBox("Enter the name of the Region.", "Region Name: North, South, East, West")
meetdate = InputBox("Enter the date of the Meet.", "Date of Meet")
End Sub

This set-up should make the variables available to all the other macros in the workbook. I have two other macros that need to use the values stored in these two variables that are entered into the two 'InputBox' statements. These two macros are in the same module and follow the 'Meetdata' sub. When I run the first macro, it recognizes the variables. When I run the second macro for some reason the variables are not recognized. To test the values returned by the variables, I placed the following code at the end of the first macro and again at the beginning of the second macro .

VB:
MsgBox(firstvariablename & " " & secondvariablename)

At the end of the first macro, the MsgBox returns the correct values stored in the variables which means that when the macro completes its run, the variables still hold their values. When I run the second macro with the MsgBox at the beginning, the Msgbox returns a blank. Somehow, the variables have been re-set to a null value. I can't figure out why the variables have been re-set to null. [URL]

View 2 Replies View Related

Passing Variable From Userform

Jan 30, 2010

I am using the following code to open a userform. Once the userform is open, the user has the option to choose a continue button or a cancel button. If the user is choosing continue, the code is not working (it always skips to the "else" portion of my "if" statement. I can't figure out why it is doing this.

View 4 Replies View Related

Passing Value Of Of EndXLUp To A Variable?

Mar 11, 2013

Dim lngrow As Long.

i want the above variable to hold the value of endxlup . so for example :

lngrow = Cells(.Rows.Count, "A").End(xlUp)

Why doesnt it hold the # of the last cell with data in column A ?

View 2 Replies View Related

Passing A Variable To A Subroutine

Dec 23, 2007

My code defines a variable: newrow. Then I call a subroutine using the Call command.
The subroutine does not recognize newrow.

It says it has value zero. How can I pass the value of newrow on to the subroutine.

View 9 Replies View Related

Passing A Variable Between Workbooks

Oct 22, 2006

I have two open workbooks, each having a single worksheet which includes a command button. The VBA code for each button performs tasks on its worksheet, then activates the other (inactive) workbook. I want to set the value of a variable in the button_click code in Workbook1 before activating Workbook2, and then use the value of the variable in the button_click code in Workbook2.

Declaring the variable as 'Public' doesn't work because the variable scope remains within its own workbook project. I must use two workbooks rather than two worksheets in one workbook.

View 4 Replies View Related

Userform Not Passing Through 1 Particular Public Variable?

Aug 20, 2014

I have a userform that is called within a sub in module. I declared a public string, "divisonb", in the module. When the userform's ok button is clicked, I define the public string through a "select case" method.

At the end of the private sub for the ok button click I have this:

[Code].....

a message box comes up with the correct string for divisonb. After the sub ends and it returns to the module I have the following:

[Code] ....

When this message box pops up, it is blank. Somehow, divisonb was redifined as blank within that 2 lines of code. All my other public strings are returned to the module with their correct values.

View 3 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 Boolean Variable To A Function

Nov 25, 2008

This might be a dumb question but is it possible to pass a boolean variable as a parameter to another function?

I have the following code that produces some compile error ("expected ="):

View 2 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

Passing A Variable Into A Different Piece Of Code

Mar 6, 2009

I have a spreadsheet that has several buttons on that run code using the cells around the code for parts of the data. eg

ColumnA1 ColumnB1 ColumnC1 Button1
ColumnA2 ColumnB2 ColumnC2 Button2

I want the buttons to run a seperate piece of code to start, but then all buttons run a similar piece of code at the end. I am hoping to be able to pass a variable from the first piece of code each button runs into the section of code that all share (which I have done as a seperate macro)

View 9 Replies View Related

Passing A Range Of Strings To Vba Variable

Apr 3, 2007

I'm trying to pass a vector with in Strings (words) to a VBA variable, but something is not working.

That's how I'm writing: ...

View 9 Replies View Related

Run Time Error 13 Passing Cell Value To Variable

Nov 28, 2006

I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:

Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)

Sub Reservoir()
Dim Inflow As Single
Dim Withdrawal As Single
Dim Evap As Single
Dim Area As Single
Dim Spill As Single
Dim Stor_1 As Single
Dim Stor_2 As Single
Dim count As Integer
Dim max As Integer
max = ActiveSheet.Cells(9, 4)
For count = 1 To max * 12
Stor_1 = ActiveSheet.Cells(count + 40, 11)
Inflow = ActiveSheet.Cells(count + 41, 4)
Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................

View 2 Replies View Related

Calculation Error After Passing Values To Variable

Jan 30, 2008

I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4.
But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway.

If ComboBox2.Text = "Pounds (£)" Then
Sheets("Claims").Range("G16") = TextBox2.Text
Else
Sheets("Claims").Range("F16") = TextBox2.Text
End If

Sheets("Claims").Range("B16") = ComboBox1.Text
Sheets("Claims").Range("C16") = TextBox1.Text
Sheets("Claims").Range("B16") = TextBox3.Text
Sheets("Claims").Range("D16") = TextBox4.Text
Sheets("Claims").Range("E16") = TextBox5.Text

Dim a As Long
Dim b As Long
Dim answer As Long
a = Sheets("Claims").[E16]
b = Sheets("Claims").[F16]
answer = (a * b)
Sheets("Claims").[G16] = answer

View 3 Replies View Related

Passing Word Variable To Excel In Office 2010

Apr 2, 2013

I'm trying to pass a variable from Word to Excel. Basically I have a Word document with a plain text content control in it. I'll have users populate this field. I know how to create a reference to that content contol in Word VBA that'll tell me what's in that content control (eg.

Code:
MyWordVar = ActiveDocument.ContentControls(1).Range.Text
).

What I can't figure out is how to pass the value of "MyWordVar" to a variable in Excel.

View 4 Replies View Related

Type Mismatch '13' On Passing Function Result To Variable

Aug 11, 2006

I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly.

Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1

With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.insert Shift:=xlDown
End With

Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True

End Sub

View 8 Replies View Related

Retain Leading Zeros Passing Variable To Cell

Mar 14, 2008

I want to know how to index a drop down list to be used for calculations.

Up until now I was using

Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id

But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.

Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.

Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id

View 6 Replies View Related

Copy ActiveCell OnChange

Sep 24, 2009

I am trying to copy the active cell from a range on sheet 1 to a new sheet ( Sheet 2 )in the next available row of Column B ONLY if the value changes from being blank.

If it was not empty I dont want it to be copied.

I cant seem to get my onChange event to copy anything.

View 9 Replies View Related

Worksheet Change Event :: Trying To Set The IntRMACol Variable

Apr 25, 2009

This is the code on the Worsheet Change event.


Private Sub Worksheet_Change(ByVal Target As Range)

'Define the RMAs range and count the RMA ships. Do the rest of the math based on this calculation.
Dim intRMACol As Integer, EndRow As Integer, intRMAShips As Integer, LastCol As Integer
Dim rngRMAs As Range, rngHome As Range, sht As Worksheet
Set sht = ActiveWorkbook.ActiveSheet
Set rngHome = Cells(1, 1)
rngHome.Activate
EndRow = Range("A" & Rows.Count).End(xlUp).Row
intRMACol = Cells.Find(What:="RMA#", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
Set rngRMAs = rngHome.Offset(1, intRMACol - 1).Resize(EndRow, 1)
rngRMAs.Name = "RMAs"
rngHome.Offset(1, LastCol + 1).Formula = "=COUNTA(RMAs)"
intRMAShips = rngHome.Offset(1, LastCol + 1).Value
rngHome.Offset(1, LastCol).Formula = "=SUM((" & EndRow & " - 1) - " & intRMAShips & ")"
rngHome.Offset(1, LastCol + 2).FormulaR1C1 = "=SUM(RC[-2] : RC[-1])"
End Sub

I keep getting stuck when trying to set the intRMACol variable. I have used the code all over the place, but only in Modules, never on a worksheet event. EndRow, the variable right above, sets correctly so I don't get it.

Also, do I have to define the Target variable? Am I going about this the right way? I basically want three formulas to calculate when any change is made on the worksheet.

View 9 Replies View Related

Worksheet Change Event :: (ByVal Target As Range) Event In Module After Creating A Sheet

Mar 27, 2009

Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?

View 9 Replies View Related

Change Event Causing Event To Fire Again

Aug 7, 2006

I have the following code, which works perfectly:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TotalDays As Integer

TotalDays = Range("C65536").End(xlUp).Row + 1

The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.

However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.

How do I add the following, to my previous code?

Range("E8").Value = Cells(7, 6) * 2.5

View 9 Replies View Related

Event Sub From Run Function Event

Aug 5, 2014

Looking for the syntax that will allow me to code an event sub routine, based off the event of a specific function e.g. findnum being run.

View 14 Replies View Related

Passing Value

Jul 7, 2006

How do I pass values between procedures? I have two command buttons in the worksheet.

If I have a procedure A that asks user to input a value "x", when a command button is pressed.
Then another command button is pressed that passes the value onto another procedure B, which iterates a msgbox as many times as the number x,

How do I pass that value x from one procedure to another?

View 7 Replies View Related

Passing Old Value's To Comment

Apr 27, 2007

I am building a tool that uses Pivot Tables (so I can't use track changes) and I have found cool code that will insert timestamp and username in the comment when a change occurs. But I need to modify the code to also take the old value and put it into the comment as well. Here is the code to register the change into the comment:

View 11 Replies View Related

Passing Different Value From Combobox

Oct 12, 2007

I'd like to know how to send a different value to a cell based on what is selected in the combobox. Like a vlookup, but from within the box.
Something like :

View 13 Replies View Related

Passing Array To VBA

Jul 19, 2009

Passing Array to VBA. I have the following

View 2 Replies View Related

Passing A Range From One Sub To Another

Dec 17, 2009

I think this is a relatively easy issue, but I don't know how to do it. I call the Sub SortRange in another Sub SortDeliver. A range (ran5) is defined in Sub SortRange that I also need to use in Sub SortDeliver. How do I pass the range or get the second sub to recognize the range?

View 5 Replies View Related

Passing Value From One TextBox To Another

Dec 28, 2009

I've a userform (UserForm1) that on closing will open one of 5 other userforms. Which of the userforms that opens will be determined at runtime, depending on an option a user has previously selected. The name of the userform is held in a cell in a workbook and is opened as follows:

VBA.UserForms.Add(sheets("Sheet1").Range("A1")).Show

where the value in Sheets("Sheet1").Range("A1") is "UserForm3"

UserForm1 contains TextBox1. How can I pass the value in TextBox1 to a textbox in UserForm3 when the name of UserForm3 is held as a variable in a cell in a worksheet?

View 9 Replies View Related







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