Fire A Sub Within A Sub
Jun 25, 2007I have seen it on here, and I have searched, but cannot find how to start it, saves me writing it twice.
View 6 Replies
ADVERTISEMENT
Event Fire For Any Workbook
Jun 16, 2007Is it possible have code in an addin that fires when, X happens in any workbook?
For example,
Any time a user tries to print a worksheet a message box pops up asking, "are you sure you want to print that"
Cell Value To Fire Macros
Oct 24, 2006I wish to fire one of 2 different macros, depending on the Target.Text value.
If the Target cell (in a named range) is blank then one macro fires, If the cell has text then another macro fires.
this is what I have
If Target.Column = 7 And Target.Row > 7 And Target.Text <> "" Then ViewPicture Target
Else
If Not Target.Column = 7 And Target.Row > 7 And Target.Text <> "" Then GetPicture Target
End If
I can only get the first if statement to fire, I get an "ELSE IF" compile error on the second "IF"statement.
It is nested in the
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Page
and I would like to get it to work with Named Range's, but not enough understanding yet.
SaveCopyAs. Code Failing To Fire.
Jan 28, 2009I have an Excel 2007 workbook with over a dozen user forms and plenty of code that I have mainly obtained from the web and tweaked to suit my needs. I am a novice with VBA, so not really up to writing anything but quite simple code.
My issue is I have some code set to fire on Workbook_Open that will save a versioned copy of the workbook, to the same network folder that the original resides in, with an incrementally increasing file name. the file is stored on a network, but access should not be an issue as I have full access and have no problem saving to this folder, also the event works fine up until I shut down Excel. I have also tried saving to My Documents to avoid the network issue, same result, worked fine as long as Excel is not closed, fails if I do.
It all works fine until I close down excel completely and re-open, where it then fails to work. There are other events happening in the same Workbook_Open sub that still work fine each time, so the sub is firing on open, but this one event fails. I get no error message at all, just no new file copy created.
The workbook is essentially doing the job of an Access database (I know even less about Access), I have a user form as a main menu and various other forms for various data entry and reporting tasks. I am exiting the w/book via a cmd button on the main menu (I've deliberately restricted users control, as many are not very pc literate). I have conducted numerous trials consisting of running the code from the VBA window, closing w/book via cmd button WITHOUT accepting the std save option and re-opening from Explorer window, closing w/book via cmd button WITH accepting the std save option and re-opening via Explorer and all worked perfectly over many sample runs. But when I closed Excel totally (Not just the w/book), created a desktop shortcut and opened from there, that line of code just doesn't seem to do anything, no error or hang or anything. The only way I can get it to function again, is to re-save the w/book (As either a new file with code edited to suit, or overwrite the original), and keep Excel open whilst only closing this w/book. It then functions perfectly again on opening.
I have enclosed below the Workbook_Open sub and some other subs that append to a user log on opening, these work fine all the time. I enclose the others in case they may have some bearing, as they are also fired from the Workbook_Open sub and show no issues at all.
Any suggestions gratefully accepted as I am struggling. As mentioned above, the ONLY part of the Workbook_Open sub that fails is the line "ThisWorkbook.SaveCopyAs newFileName". I have even added "MsgBox "The new FileName is: " & newFileName" immediately after it, and that displays new filename correctly. It seems to me to be hingeing around the SaveCopyAs event, but I don't want to assume that, being the novice I am.
Finding The Right Javascript Function To Fire On
Mar 10, 2007I am having a problem finding the right javascript function(s) to use in my macros. Use Google homepage as an example. The line
.Navigate "javascript:_dlsetp('ss=2')"
will open the page for customizing your Google page. But what is the function you fire on in the macro to execute the general search? I can send text to the search input box, but I can't find the function that runs the search.
Is there some way to quickly identify the function and the correct syntax without having to learn how all the source code in the web page works? Finding the right URLs, links, and input boxes is fairly straight forward. But not the functions.
Friction Loss Calculator For Fire Hose
Apr 17, 2008I am attempting to create a friction loss calculator for fire hose, I am using a known formula that calculates the loss based on volume and diameter for pipe. the difference that I have with fire hose in lieu of pipe is that the hose diameter changes with the pressure drop.
I have 3 variables that i input, pressure, beginning diameter and length. however
as the water flows through the line the pressure changes in turn the diameter changes, I would like to set up my spreadsheet so that the initial variables inputed yield the correct diameter, and then reference back to the initial equation and recalculate based on the yeilded diameter, and recalculates,
I can determine the friction loss at 1 foot, in turn determine the diameter at 2 foot, but I wish the spreadsheet to work the calculation over the entire length.
Macro Causing Custom Function To Fire
Sep 3, 2007I have a large file, part of which amongst other things calculates life expectancy from a range of q(x) values (proportion of people that die moving from age (x) to (x+1). Life expectancy is calculated using a user-defined function (below).
My problem is that whenever I run a macro that changes the file, even parts of the file that don't affect the cells using the life function, it jumps into the life function. (An example: copying and pasting values on a different sheet). This is a hassle when stepping through other macros using F8, not to mention the time cost.
Some further possibly necessary information: one macro uses the GoalSeek application to set the target cell that contains the life function
By the way, this didn't use to happen in older versions of a similar file. When running the GoalSeek macro to change target life expectancy it did, but not for any other macro.
Here is the function:
Function life(data As Range)
' Aims to calculate Life expectancy from Qx values
' It assumes first value of Q is Qb, then Q0 to Qmax
Dim Nobs As Integer
Dim j As Integer, i As Integer
Dim q() As Double
Dim L() As Double
Dim T As Double, le As Double
Nobs = data.Rows.Count
Fire Chart VBA Code On Validation List Choice
May 8, 2009very complex spreadsheet for weight & balance calculations. It's to the point where everything works perfectly in Excel 2007, but it must be used primarily with Excel 2003. Discovered that a crashing problem had to do with condtional formatting, that's all been cleared and will soon be fixed, but there's one that I just can't quite figure out.
I'm using the worksheet change event to trigger the update of charts... In this case, it's looking at a particular cell that has data validation on a dropdown as the trigger. Works perfectly in 2007, and if I put a msgbox prompt in to be launched by a change of that cell, it launches. I can put the chart update code in a separate sub and launch it manually every time, but I cannot call it from the worksheet change.
I've included attachments showing what I believe are the relevant bits of code -
Fire An Event Or Action On Visual Basic Editor Close
May 26, 2009Is there a way to make a macro fire when you close the VB editor. For example,
I would like one of my pre- recorded macros to start as soon as I close the VB editor.
Check Multiple Cells, Fire Macro Based On Result
Jul 14, 2007I have three cells where a user will input data, in some cases (2T Weld Condition) they will only enter in B12 and C12, but in the case of a 3T weld they will also enter data in the D12 cell. I then use a formula to check for the thinnest material and that is entered into another cell with a formula, B14. I then need to check the value in B14 to verify if it is above zero, but below 0.65 (mm). If it is then I would like to have a message appear on the screen notifying the user that they are outside the acceptable range.
I cannot figure out how to use the information in cell B14 because it is a formula and my code only works with a direct value. The code I am using works if I point to one of the three input cells, B12, C12 or D12. How do I use the information in B14 to work with the code below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$12" Then
If Target.Value < 0.65 Then
Run "MyMacro"
End If
End If
End Sub
MyMacro loads a userform with buttons, etc.
Worksheet_Change Event Wont Fire When Target Changed By ListBox Selection
Dec 5, 2008Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("R1C1")) Is Nothing Then
'do something
End If
End Sub
Now, that Private Sub works fine if you change the value of R1C1 manually or from another macro.
But if R1C1 is the cell linked to a list box, nothing will happen if you change its value by selecting different items in the list box.
Worksheet_Change Code: Determine How To Get The Code Below To Fire Whenever Cell J10 Is Populated
Jul 30, 2009I am trying to determine how to get the code below to fire whenever cell J10 is populated and do nothing when cell J10 is not populated but I can't quite get it. (Cell J10 is manually changed and is not changed based off of a formula)
View 4 Replies View RelatedFire VBA, On "Locked,Protected And UnSelected Cell?"
Apr 29, 2006I want a VBA function to fire "ONLY" when a cell is changed.Peramiters:
Data ertry range is full, Last cell in SubTotals range has changed from $0.00 to anything >, Last 3 cells are Locked, Protected and Unselectable Attached is a copy of the work book. I have posted on VBA Express and we have tried to solve this problem, We have come a long way. You might want to read the history link above.
Change Event Causing Event To Fire Again
Aug 7, 2006I 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