Run A Macro Called "PriceForm" Instead Of Clearing The Cells

May 30, 2009

Code to run a macro called "PriceForm" instead of clearing the cells.


Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("T5:X50").Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

View 9 Replies


ADVERTISEMENT

Macro Called For Copying

Feb 23, 2007

I have a macro called "Copying" and this runs perfectly well when run on its' own.

I have now created the following peice of
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Sheets("form").Range("B14").Value x Then
x = Sheets("form").Range("B14").Value
End If
Application.Run "Copying"


End Sub
Which (should) run the "Copying" macro if cell B14 is changed........here is my problem.

It seems to run this macro over and over again. How can I get it to just run it once?

View 9 Replies View Related

"PC" To Show Up If I Type A Sentence Containing "Called Parent", "Called Dad", Or "Called Mon"

Dec 2, 2009

I need "PC" to show up if I type a sentence containing "Called Parent", "Called Dad", or "Called Mon". Here is what I'm trying.

View 2 Replies View Related

Skip Lines If Macro Is Called From Another Macro?

Feb 13, 2014

I am calling sub B from sub A and want to skip some lines in sub B (only when it is called from sub A).

View 3 Replies View Related

Suppress Message When Macro Called By Another Macro

Aug 15, 2007

I have a Macro (AutomateReport) that, among other things, calls and runs a macro (ReportStep1) in another workbook.

ReportStep1 includes three prompts asking the user to press "OK" to confirm that the data included is correct. When I call ReportStep1 as part of my larger macro I would like to suppress these prompts or automatically respond "OK" so that I don't have to be bothered with them.

SendKeys works sometimes, but I am hoping for a more reliable method.

View 3 Replies View Related

Macro To Delete All Worksheets Except For One Called Main

Mar 26, 2007

Does anyone out there have a macro that will delete all worksheets except for one specific one? In this case I want all worksheets deleted except for one called "MAIN".

View 9 Replies View Related

To Create A Function Macro Called FSTD

Nov 5, 2008

i need to create a function macro Called FSTD that inserts words into a cell and conditionally formats them depending on th number in the cell on the left.

in the sheet there are ranges that the number will fall between.

Example

Cell B1 is the cell were i want to run the function in
Cell A1 is the cell with the number that is used in the function

Cells A3:A7 are that ranges that i need to use in the function

E.g. if the number in A1 is less than A6 = i want "P" to appear in the cell with a red backround

if the number is greater than A6 but less than A5 = i want "M" to apear with an orange back round

if the number is Greater than A5 but less than A4 = i want "E" to apear with a green backround

even if it is possible to do without changing the backrounds, just to insert the letters

View 9 Replies View Related

Clearing Unprotected Cells

Jul 19, 2002

I have a spreadsheet that has I have protected. On this sheet I have a button labeled Reset Form. When you click the button it it goes to each Unprotected Cell and deltes the contents. I created this by starting the macro and then tabbing to each and every cell and deleteing the contents I then ended the macro...

Is there a code that I can use for to clear all unprotected cells as opposed to creating the macro manually?

View 9 Replies View Related

Clearing Contents Of Cells

Apr 27, 2007

My computer runs on Excel 2003 but my Laptop is on Excel 2000, the problem is I have the below code on a spreadsheet I wrote on my Computer which runs fine but when I open it on my Laptop it comes up with a run time error it doesn’t like the clear contents line. If some one could explain to me why it is happening and how to get round the problem so it will work in Excel 2003 and 2000.

Private Sub Workbook_Open()
Application. ScreenUpdating = False
Sheets("data").Visible = xlVeryHidden
Sheets("Incidents").Visible = xlVeryHidden
Sheets("Front Sheet").Select
Range("A17:S17").Select
Selection.ClearContents
Open1.Show
Range("A17").Select
Application.ScreenUpdating = True
End Sub

View 2 Replies View Related

Clearing Cells Containing Specified Words

Apr 29, 2008

I am trying to clear the contents of cells in a very large spread sheet containing certain words. I am running the following macro and it will only do the first part, so it will clear cells containing "deceased" but stops at "esa". There may be any number of other words or text in the cell and I want it to delete cells with "esa" by itself so for ex. not delete cell containing "vanesa". I am very amateur so I am sure this is something simple but I cannot find the answer or figure it out for myself.

Sub DeleteAll()
Dim lCount As Long
Dim rFoundCell As Range
Dim rLookRange As Range
Set rFoundCell = Range("A1")
Set rLookRange = ActiveSheet.UsedRange
For lCount = 1 To WorksheetFunction. CountIf(rLookRange, "*deceased")
Set rFoundCell = rLookRange.Find(What:="deceased", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
rFoundCell.ClearContents.......................

View 6 Replies View Related

Macro- List Of CSV Files In A Folder Called Balances

Dec 18, 2008

creating a macro, I have a list of CSV files in a folder called balances, i need a macro that will look into each CSV file and grab all p'folios which have a negative balance in Col D and line them up as per example shown below. The extracted data needs to be copied to file named "Workings" S:RecsalancesWorkings

All data in CSV file looks like this, where header row is row 1 starting at A1

CSV data

CSV filenames all start with ddmm so e.g. 0111

End DatePfolioA/c CodeA/c Native at End1/11/20081GRVMAAUDCUST 1/11/2008AFSBGEAUDCUST 1/11/2008AFSMBEAUDCUST 1/11/2008AFSPEEAUDCUST 1/11/2008AFSTRNAUDCUST 1/11/2008AMPFSVAUDCUST 16,565,587.811/11/2008GIORIQAUDCUST 155,642.251/11/2008GIORISAUDCUST 1/11/2008PINESIAUDCUST 5,654,632.861/11/2008WACCGIAUDCUST 7,457.561/11/2008WCTARIAUDCUST -3,923.721/11/2008WCTARWAUDCUST 1/11/2008WCTASIAUDCUST -73,456.201/11/2008WCTBGFAUDCUST 1,662.651/11/2008WCTDAIAUDCUST 1/11/2008WCTFUNAUDCUST 27,665,698.291/11/2008WCTGMIAUDCUST 31.251/11/2008WCTROWAUDCUST 6.961/11/2008WCTTHSAUDCUST 22,030.01

Macro to line up data as per below

P'folioDateWCTARIWCTASIWSTFDGWTDTADWTFFI3WTGTFAWTRLGD1/11/2008-3,923.72-73,456.20-4.18-136,496.19-562,967.29-2,319.40-15.762/11/2008-3,923.72-73,456.20-4.18-136,496.19-562,967.29

View 9 Replies View Related

How To Stop From Clearing Hidden Cells

Aug 10, 2014

I have cells, or columns hidden when I do not need them, but I have to a data entered in those cells to make my spread sheet work. Usually just 0.

When I do the all clear or " clear all contents" operation at the end of the day, it clears the hidden data/cells too. Is there anything I can do to stop excel from clearing those hidden cells?

View 3 Replies View Related

Clearing Merged, Multiple Cells

Mar 25, 2009

I have a macro where somone fills in a form and then it copies the data in the form to a database (another worksheet). Once the macro is run and has copied the form to the database, I then clear each cell seperately using the below code :-

View 5 Replies View Related

Clearing Selected Cells On A Form

Oct 15, 2013

I have compiled the following code with bits from here there and everywhere to clear the user entered details on a form. My problem is that I need to retain or reset to "Please Select" the cells with drop down lists.

In list form, I am trying to;

Unprotect the worksheet,
Select the cells with drop down lists and lock them,
Delete the contents of unlocked cells,
Selecting and unlocking the cells with the drop down lists ready for re-use,
Re-protect the worksheet.

(I probably should mention that my "Form" is just a formatted worksheet that looks like a form on the screen and when printed, not any sort of inbuilt Excel function that I have read about somewhere, probably here)

Code:
Sub ClearUnlockedCells()
'
' Clearform Macro
' Clear entered data from Form.
'
Dim WorkRange As Range
Dim DVRange As Range

[code].....

View 4 Replies View Related

Clearing Cells Based On Condition

Oct 3, 2008

I've created (with the help of jmthompson from here) a macro to pull an entire row of data and copy to another sheet (Sheet1) if column U has "YES" in the cell.

Now I need to clear the contents of cells in range B-U after the copy has occurred.

Here's the current macro: ...

View 9 Replies View Related

Clearing Contents Of Specific Cells

Dec 7, 2009

I created a macro to clear cells but I can't get it to clear a number of ranges. It will only clear single ranges.

Sub DeleteStuff()
Dim c As Range
For Each c In Range("G6:G10", "H6:H10", "J6:J10")
If c "" Then c.ClearContents
Next c
End Sub

View 9 Replies View Related

Clearing Forms Check Box With Macro

Sep 5, 2009

I have a macro to that clears my excel cells. I have added "forms check boxes" that need to be cleared at the same time. Can this be done with code that i can add to the macro or does it need to be seperate?

View 9 Replies View Related

Excel 3003 :: How To Prevent To Stop Macro Called From A Function

May 3, 2014

I am trying to use the simplest code possible (being a novice) to execute a macro which makes a copy of Cell A1:A4 and paste its values to Cells B1:B4 if cell B5=1 (or whatever). The Macro with copy and paste works (I recorded it and in my testing it was ok). And also the function, that contains If-Then status of cell B5, works and calls correctly the Macro.

Yet when the macro is called by the function (=CopyMyCells(B5)), the Macro (MacroCopy) runs - I tested this with a Msgbox - but stops just in doing what I need, i.e. in doing the copy-paste process. Here the code which is placed in a module of the proper Worksheet (Excel 2003, on WinXPSp3):

[Code] ........

View 6 Replies View Related

Run Time Error 1004 (macro Called From Access Module)

Nov 19, 2008

i am running a macro thru vba (beiing called from a ms access module) and am getting a RuntTimeError 1004.

the code opens a workbook...then open a second workbook (which houses the macro) then activates the desired worksheet and call the Maco via the run command but errors out.

if i open the workbook and set focus on the desired sheet ....tool>macro>desired macro name it runs fine.

the line of code in the macro is: "ActiveCell.Offset(1, 0).Activate"

View 9 Replies View Related

Clearing Unlocked Cells On A Protected Worksheet

Dec 6, 2007

I have a workbook with a series of sheets that have tables for entering data. The table row and column labels and formulas in certain cells of each sheet are locked and some sheets are hidden.

When opening the file I want to clear all cell contents (interior color, comments, data, etc) in all the unlocked cells on each sheet that is not hidden.

I tried protecting the sheets first so only the unlocked cells would be accessible, but when I run the code below I get an error saying that the clear contents etc. cannot be performed because the sheet is protected. If it is not protected, everything gets wiped out.

How can I keep the locked stuff but clear the unlocked cells?

For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then

ws.Protect Password:="aaa" 'Protect each sheet
ws.Cells.ClearContents 'clear content of any unprotected cells
ws.Cells.ClearComments 'clear any cell comments
ws.Cells.Interior.ColorIndex = 0 'set background colour to no fill
Active.Cells.Range ("a1") 'make the active cell the top left

End If
Next ws

View 9 Replies View Related

Deleting / Clearing Multiple Cells In Target Range

Jun 2, 2014

I have a target range for a worksheet change. Then when finished I highlight the data and press 'delete' I get an error within the code.

View 2 Replies View Related

Clearing Of The Cells Clears As Well The Copying Of "B30"

Oct 14, 2008

Sub test()
Range("B30").Copy

Range("A30:B30").ClearContents

End Sub
Problem: the clearing of the cells clears as well the copying of "B30"

View 9 Replies View Related

Calander Control Called In VBA

Jan 9, 2008

I'm using the calander control in Excel which is called in VBA when a user clicks on a cell in a range - the code is as follows:

' Calander for Activity Start Date and end dates
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, [H11:I30,H33:I52,H55:I74,H77:I96,H99:I118,H121:I140,H143:I162,H165:I184,H187:I206,H209:I228,H231:I250,H253:I272,H275:I294 ,H297:I316,H319:I338,H341:I360]) Is Nothing Then
If Intersect(Target, [K11:L30,K33:L52,K55:L74,K77:L96,K99:L118,K121:L140,K143:L162,K165:L184,K187:L206,K209:L228,K231:L250,K253:L272,K275:L294 ,K297:L316,K319:L338,K341:L360]) Is Nothing Then
Calendar1.Visible = False
Exit Sub
End If
End If...........

View 9 Replies View Related

Can A Subroutine Be Called Within A Function

May 12, 2006

1. Can a Subroutine be called from within a user-defined Public Function? How?

2. Is there a difference between calling a subroutine with a 'Call Sub_Name' statement, vs. calling the subroutine with an 'Application.Run'(?? or similar) statement?

View 5 Replies View Related

Determine Which Check Box Called Procedure

May 21, 2009

If I have several Forms check boxes assigned to the same procedure, is it possible to tell which check box called the procedure?

View 6 Replies View Related

Function Being Called At Inappropriate Time

May 29, 2009

Function being called at inappropriate time
I have the following code in the active worksheet:

View 4 Replies View Related

Called Or Directly Excecuted Routine

Dec 10, 2009

I want to thank you all for the solutions/support I received in completing my (for me) difficult workbook.

I still have a tricky problem. In the example below, can Multibeep "know" when it is being called from Beep versus being excuted directly? And if so can I direct a different sequence?

View 10 Replies View Related

Passing Value Of Convert To Other Procedures That Are Being Called?

Jul 2, 2012

I am running this code it is rather long, but I need to show it all ?

Code:
Public Sub Monday()
Dim mName() As Variant
Dim lName() As Variant
Dim fName As String

Call lower(CStr(mName(q)), fName, CStr(lName(q)))

[Code] .........

How to do is to pass the value of convert to the other procedures that are being called? As you can see I know how to pass variables down to "Sub-routines" but how would I pass the variable up?

View 8 Replies View Related

Series Of Subs Called From Function

Apr 15, 2013

I have a series of procedures I am calling from a function that each run fine when executed individually. These procedure are titled: DataValidation, Test1, Test2

The function will not run past Test2- I do not get the msgbox "Stage 2"

Code:
Public ArrayD
Public ArrayD2 As Variant
Public RngD3 As Range

Public Function Test(arg2 As Range)

DataValidation arg2

[Code] ............

View 4 Replies View Related

Excel Called From VBS Does Not Fully Exit

Nov 5, 2009

I am trying to sort a csv file through a VBScript. My problem is that Excel continues to run as a process on script completion. Running multiple variation of this script results in multiple instances of Excel being present in the WinXP Process Manager.

Set xlObj = CreateObject("Excel.Application")
xlObj.Visible = false
xlObj.Workbooks.Open "C:Test.csv"
xlObj.ActiveWorkbook.ActiveSheet.Range("A1").Sort xlObj.ActiveWorkbook.ActiveSheet.Range("A1"),,,,,,,0
xlObj.ActiveWorkBook.Save
xlObj.ActiveWorkBook.Close true
xlObj.quit
set xlObj = Nothing
As a second part to my question, is there any way to modify the code to allow multiple field sorts, i.e., A1 is Primary, C1 is secondary?

View 9 Replies View Related







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