Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Macro To Bring Up A Pop Up Box


how I would go about writing a macro that would pop up if a value in a certain cell is greater than a set value. For example, if the cell b1 has a value of >50, I would want an alert box to pop up saying "Over Range". Ideally this should be automatic as soon as another value is entered into cell a1.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Macro To Bring Up Save As Dialog Box
I am modifying a corporate macro that is used in to create a standard technical letter. This standard file has 6 macros, that once used to create the letter, are never used again. I know, the first thing that jumps out is to create an add-in. I'd prefer not to got hat route at the moment for various reasons.

What I'm looking to do is after the letter is created, and the print macro is called and prints the letter, all macros are removed (already found help for this on this site), then check to see if the file has been saved, or still has the template name, and then either save or bring up the save as dialog box for the user to save the file with the appropriate name and folder.


View Replies!   View Related
Bring In The Values From The Table
I would like to download historical stock quotes from yahoo and then bring them into a worksheet. I have used the Webbrowser control to bring in the Yahoo page but I have no idea on the code to use to bring in the values from the table.


View Replies!   View Related
Bring Values Beneath Each Other...
I have a sheet made of 3 columns, every column has several rows, I am trying to bring the values of the second column beneath the values of the first column and then the values of the third column beneath them all. Attached are 2 sheets showing the current and required scenarios

View Replies!   View Related
Using If And Formula To Bring Back Company Name
Name Qtr Year
Org 112007
Org 222006
Org 332006
Org 442006
Org 542007
Org 612006

I want the code to say, If Qtr = 1 And Year = 2007, then bring back Org Name, If Qtr = 2 And Year = 2006, then bring back Org Name and so forth.

The code should loop through a range of constant data (Region Name), until the Region Name is blank.

Dim OrgCell As Range
Dim OrgTargetCell As Range
Dim RegionCell As Range
Set OrgCell = Sheets("DATA Removed").Range("A2")
Set OrgTargetCell = Sheets("Overdue").Range("A3")
Set RegionCell = Sheets("DATA Removed").Range("C2")
i = 0

OrgCell.FormulaR1C1 = _
"=IF(AND('DATA Removed'!R[-1]C[11]=1,'DATA Removed'!R[-1]C[12]=2007),'DATA Removed'!R[-1]C,0)"
Do
OrgTargetCell.Offset(i, 0).Value = OrgCell.Offset(i, 0).Value
i = i + 1
Loop Until RegionCell.Offset(i, 0).Value = ""
End Sub


View Replies!   View Related
Bring All Rows Into My Template Spreadsheet
I've created the code below from scratch (ever so proud of myself even though it's basic lol)

Dim N As Long
Dim LR As Long
N = Range("A2").Value
LR = Application.WorksheetFunction.CountIf(Worksheets("Overview").Range("AJ:AJ"), N)
For N = 1 To LR
Here, 'N' is a project number. This code succesfuly counts the number of sub-tasks linked that that project in my data sheet. Now I want to bring all those rows into my template spreadsheet, but dont know how to start

View Replies!   View Related
If Any Cell Is (red) Has A Color Index Of 3, Bring The Whole Row To The Top
I have a worksheet with several columns and 1,000's of rows. I have code that makes all "good cells" grey (color index 15) and all "bad cells" red (color index 3).

I would like to do 2 things...

1. If ANY cell is RED, cut the WHOLE ROW and "insert cut cells" below the header row (even if ALL other cells are grey), then repeat the process up the whole worksheet until ANY row with a red cell is at the top.

2. Create a new worksheet named "Trouble Cells", copy the header row along with any rows with red cells.

I would like to keep the formatting the same (for example, the title row is always yellow and is "28" high and all other rows are a height of "12").

I would also like to keep the column width of each column in the new worksheet as well.

Excel 2002

View Replies!   View Related
Double Click To Bring Up Form
I'm using a little sub routine to bring up a pop up calendar when i click in a cell, everythings lovely but it annoyingly comes up when i select a clolumn or row that also contains that cell. modification to require a double click to display the calendar?

View Replies!   View Related
Bring Back Complete Printer Name
create an array of active printers. I edited the code to write the array to a range, then populate a data validation list with the printer names. All works great. The goal was to select a printer name from the list and run a macro to print some sheets. However, the function doesn't bring back enough of the name to do this. how to edit the function to bring back the whole name.

For example: after running the function the 1st printer returned ="\rizzoHQ-ENG-04"...when i record a macro to print to this printer it needs ""\rizzoHQ-ENG-04 on Ne05:". Here is the function unedited, I bolded the part i think is controlling the name:

View Replies!   View Related
Sort : Bring All The Baskets Under Column
My rows and columns are as below
_____________________________________________________
A B C D E F
____________________________________________________

Basket 1 3 Basket 1 4 Basket 2 5
Basket 3 7 Basket 3 2 Basket 9 4
Basket 4 2 Basket 5 2 Basket 11 1


I want to bring all the baskets under column A in one row and Oranges in B, Apples in C and Lemons in D like this

Items Oranges Apples Lemons
________________________________________________________
Basket 1 3 4 0
Basket 2 0 0 5
Basket 3 7 2 0
Basket 4 2 0 0 and like wise.

What is the best way to do this.

View Replies!   View Related
Format To Bring Decimal Point Forward
Is there anyway to be able to bring decimal point forward without affecting the numbers using any formatting, which will still be there even after refreshing the query.

For example, 0.0531 to 53.1

View Replies!   View Related
If And Vlookup Formula (bring Up The Corresponding Product Description )
I'm trying to get a cell {in the attached spreadsheet 'Order Form' Sheet- Cell 'B13'}, to bring up the corresponding product description {'Products' Sheet- Column C}. For the cell that is to bring up the product description {'Order Form' Sheet- Cell 'C13'}, the following is the formula I'm trying to use, but is coming up "#N/A":
=IF(C13>1000,VLOOKUP(A13,Products!$A$2:$D$12,3),IF(C13>287,VLOOKUP(A13,Products!$A$14:$D$25,3),IF(C1 3>0,VLOOKUP(A13,Products!$A$28:$D$38,3)," "))).

I haven't attempted the "Cost/Bottle" formula yet {'Order Form' Sheet- Cell 'D13'}, but it needs to correspond the product number {'Order Form' Sheet- Cell 'A13'} and the quantity ordered {'Order Form' Sheet- Cell 'C13'}, with the cost/bottle on the that quantity found on 'Products' Sheet, Column D.

View Replies!   View Related
Bring Data From Certain Row Based On Cell Number
I'd like to bring data from Sheet2 to Sheet1. If value at Sheet1 f2=5 it should copy from sheet2 b5:k5 tosheet1 a2:j2 If value at Sheet1 f2=10 it should copy from sheet2 b10:k10 tosheet1 a2:j2 etc.

View Replies!   View Related
Bring To Front (Most Recent Workbook Opened)
I've got a macro that searches & opens a most recent file. I'm trying to figure a simple way to bring to the front the workbook which is opened by the macro.

View Replies!   View Related
Vookup: Bring Back The Info In The Last Populated Cell On The Row
I want to lookup some data and bring back the info in the last populated cell on the row, rather than column 4. I have written the below but it is bring back info from Column Index number 4.

=VLOOKUP(D4,'A2'!A:Z,4, FALSE)


View Replies!   View Related
Multiple Search Criteria To Bring Back Data
I have a dataset which is dumped into my model every month. The Report tab is a "user form" whereby the user can select multiple search criteria. The dataset is then filtered according to the search criteria entered by the user.

Problem is, that if the user only selects 1 of 3 search criteria and the other 2 cells are left blank, the filter filters on these blank cells and doesn't retrieve any data. Any ideas how I can overcome this?

Sub PopulateReport()

Application.ScreenUpdating = False

Dim MyFilter1 As String
Dim MyFilter2 As String
Dim MyFilter3 As String

MyFilter1 = CStr(Sheets("Report").Range("C2").Value) ' convert cell value to string
MyFilter2 = CStr(Sheets("Report").Range("C4").Value)
MyFilter3 = CStr(Sheets("Report").Range("C6").Value)

Sheets("Waste").Select

Dim Rw As Long
Dim Rng As Range

Rw = Range("A65536").End(xlUp).Row
Set Rng = Range("A1:W" & Rw)

With Rng
.AutoFilter
.AutoFilter Field:=20, Criteria1:=MyFilter1
.AutoFilter Field:=2, Criteria1:=MyFilter2
.AutoFilter Field:=13, Criteria1:=MyFilter3
End With

how to display my excel worksheet using HTML maker as I've downloaded the software but have no idea how to use it!

View Replies!   View Related
Look Through A Sheet, Find A Text Reference, Then Bring Up Something In A Cell
I'm in the leadup to my A2 ICT controlled conditions piece where i have to make an Excel system. My chosen subject for this is to use the school house points system. I'm going to make a system where the results of house matches are inputted and points are awarded accordingly.

I just have a couple of queries after looking at my initial designs.

1. Is it possible to get excel to look through a sheet, find a text reference, then bring up something in a cell located in the same row? For example, for it to locate one of the House team names, then go along the row and find the ammount of house points they have in order to make a total for them. Is this also possible over a number of sheets? (In order to keep a running total on a separate sheet).
E.G. House, Games Played, Wins, Losses, Draws, House Points

2. For the house cross country event they use a different system which differs from everything i have designed. What i basically need to do is be able to use a drop down against each finishing position where you can select one of the four house teams, then it takes the points won for that finishing position and puts them into a table.

View Replies!   View Related
Search Page To Bring Back All Transactions Based On A Primary Key
I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:

Account NumberDateComments2343566/2/2009 $ 111.43 3453465/1/2009 $ 89.34 5676552/5/2008 $ 643.23 8078989/3/2008 $1,245.34 12543612/5/2008 $ 56.65 2343562/2/2009 $ 343.54 3482459/9/2008 $ 78.76 9345641/2/2009 $ 356.22 2343565/3/2008 $ 529.66

The idea is to enter an account number like 234356 click a button and bring back:

Account NumberDateComments2343566/2/2009 $ 111.43 2343562/2/2009 $ 343.54 2343565/3/2008 $ 529.66

I got the button part done and using vlookup it brings back the first line. The problem is that it won't bring back all the rows just the first one.

View Replies!   View Related
Search Criteria Bring Values Back To A Results Column
i have the following macro which finds instances of a search criteria and brings them back to a results column. what i need is instead of having the word GD: appear before each result i need it once at the begining of the results

LastRow = Sheets("design owb").Cells(Rows.Count, 10).End(xlUp).Row
Application.ScreenUpdating = False
For i = 26 To LastRow
Target = Sheets("design owb").Cells(i, 10)
With Sheets("design")
For J = 2 To 54
For K = 4 To 11
If Target = .Cells(J, K) Then
Sheets("design owb").Cells(i, 11) = Sheets("design owb").Cells(i, 11) + "GD: " & .Cells(J, 15) + " | "
End If
Next K
Next J
End With
Next i
Application.ScreenUpdating = True

View Replies!   View Related
Search For A File And Bring Back Data To Master Spreadsheet
To set up the problem, I have a folder that contains files that are all named numerically, ex. 08-100, 08-101, etc. Each file is identical in format but contains different data, ex. cell B1 is alway "material weight", cell B2 is always "estimated man hours" and new files are added weekly.

I am trying to set up a master spreadsheet that all I have to do is enter the file name (08-102) in the first column, and the second column will return the data in a specific cell of that file.

View Replies!   View Related
VLookup To Find The Customer And The Dates And Then Bring Me Back An Asterisk In A Separate Column
I have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.

I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.

Here is my formula now:

=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)

J2 is the customer name
L2 is the week
"East Data" is the spreadsheet with all of the promotions and customers.

View Replies!   View Related
Mixing VLOOKUP And LEFT Functions (bring Up The Model Type Of A Product Based On Its Material Code)
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:

View Replies!   View Related
Cell Referenceing (display The Value In The Cell Offset By X So I Can Bring The Numbers From Sheet1 Over)
Sheet1 is a list of names, then a few columns of numbers.
Sheet2 contains calculations based of the numbers in sheet1.

Each sheet from 2 on is names after the names in sheet1 column A...
A large cell at the top of each sheet contains the formula to display the sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

So what I want to do now is...
in cell, look for the cell in sheet1, and display the value in the cell offset by X so i can bring the numbers from sheet1 over.

In plain english the cell would say like...
"look in sheet1 for "Name" then display the cell next to it"

I know to look in the next sheet its "=Base!B2" if i wanted b2 on sheet base.
I know to use "=OFFSET(Base!B2, , 1, , )" too look at B2 and go right 1 column.
But the issue is i dont want Base!B2.. i want whatever cell in Base column A that has the text that A1 has.

View Replies!   View Related
If Statement: If The 2009 Price Is .50 Or Less Away From The 2008 Price, Bring Back "Check"
I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:

2008 2009
$23.95 $24.15

Using excel 2007

View Replies!   View Related
Conflict Between Auto Save&close Macro And Show/hide Sheets Macro
I am trying to make a save&close workbook macro.

I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).

The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.

If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.

The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)

The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.

View Replies!   View Related
Macro Coding: Add A Code To The Sort And Paste Macro That Will Open The Second Spread Sheet
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the

Windows("estimate sheet one.xls").Activate

View Replies!   View Related
Userform To Control Macro Features. Macro = Search For File Type
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.

The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.

I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet

I just don't have any idea how to sync the two.

If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.

The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.

'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.

'CB1_Find_Files' should activate the macro to find any files for the specified criteria.

There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.

View Replies!   View Related
Macro Paste- Macro To Get The Values From Cells D29 And H24 In The Resource Calculator Sheet
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.

Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.

Is there a way to do this?

I've attached the file for you to see.

View Replies!   View Related
Worksheet Change Macro Takes Too Much Time When Run With Update List Macro
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.

My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?

View Replies!   View Related
Macros Not Visible: End User To Be Able To Run The Report Multiple Times By Choosing The Name Of The Macro From The Macro Menu
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???

View Replies!   View Related
If Statement In Macro: Macro To Change A Range Of Cells Colours Based On A Single Cell?
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?

2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?

View Replies!   View Related
Macro Doesn't Work On Button, But Fine From Macro Menu!
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.

When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.

The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.

Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!

View Replies!   View Related
Macro To Export And Import A Module And Assigning Different Macro Automatically
Macro to export and import a module and assigning different macro automatically ...

View Replies!   View Related
Macro Stops Running The Macro Completely And Hangs In The Middle
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.

They are four PC's along with our Managers laptop that run this Macro daily.

About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.

We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.

I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.

I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.

View Replies!   View Related
Running A Macro When A Value Is Reached In A Specific Cell Used To Call Up Macro
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.


View Replies!   View Related
Delay Macro (Put In A Statement Within A Macro That Populates Cells With The Values)
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.

I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'

After = Before values (in this mock)

When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.

View Replies!   View Related
Macro Recording 2007: Record A Macro To Insert An Autoshape
I recently installed Excel 2007, and have shared others' joy in searching for things on the ribbon. I tried to record a macro to insert an autoshape. Excel creates the macro, but drawing the autoshape is not recorded. I'm sure I'm missing something obvious ...

View Replies!   View Related
Macro Disable/enable Still Appears After Macro Deleted From Workbook
I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.

View Replies!   View Related
Macro To Delete Vba Code - Macro Security Warning
I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.

My worksheet runs a lot of code, then deletes all macro code and saves itself.

The problem is the next time i open I still get the macro security warning!

I've checked thoroughly and there is definatley no vba left anywhere.

If I open the document, enable macros, and save it, then open it again, I no longer get the warning.

View Replies!   View Related
Macro - Repeat Macro A Specific Number Of Times
I have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.

However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.

View Replies!   View Related
Macro SortWorksheets Stopped By Each Worksheet Until The Macro Is Ended
I have in my Workbook_open a macro,called Workbook_SheetActivate
with a line : Msgbox ActiveSheet.Name ....

In a sheet I have a macro SortWorksheets ....

My problem is , when I run the macro SortWorksheets it always stopped
by each worksheet until the macro is ended, this because the macro by opening workbook.

How can I make the macro SortWorksheet in one run?


View Replies!   View Related
Macro That Takes Workbooks Or Files And Runs Them Through Another Macro
i need to make a macro that takes workbooks or files and
runs them through another macro. i already have the 2nd macro
done and it is working perfectly i just need to know how to make the one
that finds the other files and runs them all through the macro i already made. My boss said that he will have about 150-200 files to run through this macro.

View Replies!   View Related
Macro Progress Bar For Macro Taking Long Time
I have a macro that is taking a long time, so long that I think something is wrong but I dont really have a great way to check. I want to put up a little window showing the progress either by saying "i'm on row XX" or having a progress bar.

Looks like msgbox requires action before the code continues so it is not a good thing to use if you want to get progress. My formula looks for bad values in a data file and fixed them. It takes forever. I would like to have a box there that simply tells me what row it is working on.

Clearly I dont want to hit the OK button for it to continue processing. I want it to update the message box on its own and then take away the message box when it is finished processing.

View Replies!   View Related
Creating A Macro, That Creates A New Button, Which Itself Runs A Macro..
I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.

I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)

View Replies!   View Related
Open File Macro: Same Directory As Macro Workbook
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.

Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"

View Replies!   View Related
Restrict Macro Keyboard Shortcuts To The Workbook Containing The Macro
I've created Keyboard Shortcuts to run Macros in "Workbook A". Problem is, if I have "Workbook B" open in the same Excel instance/process as "Workbook A" the Keyboard Shortcuts will run the Macros in "Workbook A".

Is there a way to restrict the Keyboard Shortcuts to only run the Macro if Workbook A is active?

Or is there a way to code the Macro to run its procedures only if Workbook A is active?

View Replies!   View Related
Pause Macro - Select From A Drop Down - Restart Macro
I am trying to pause a macro on a protected sheet, select 2 adjacent cells (initially protected), utilize an existing drop down box to select a name from the list, copy the name from the list into the range of cells, then re-start the macro.
I had no problem when there was just one name (see John Smith below). I tried to use the InputBox command but needed to actually type in the name.

ActiveSheet.Unprotect
Range("C27:D27").Select
'ActiveCell.FormulaR1C1 = "John Smith"
' Application.CutCopyMode = False
' ActiveSheet.Paste
'Range("c27:d27") = InputBox("Enter value")

View Replies!   View Related
Macro Fixes Macro: Copies Tabs From The Spreadsheet
I am working on a macro that copies tabs from the spreadsheet the macro (button) is located in, creates a new book for them, and then pastes values so that no formulas still exist. The problem I am having is with named ranges. The named ranges do not reference the new sheet, but rather the old spreadsheet. This is fine because the new file is solely for presentation, however, one macro (which selects print ranges based on user selected checkboxes) tries to reference the old spreadsheet once it runs. I would like the macro that I currently have working, to also make this macro in the NEW spreadsheet so that it can still highlight the correct print area when used. The macro does not reference the name of the workbook at any point, it is purely "Range("ai10") type references so I don't understand why it is trying to open the old sheet back up.

View Replies!   View Related
Macro Delete Page (delete A Sheet In A Macro But When I Run The Macro)
I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.

Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete

View Replies!   View Related
Macro To Select VbYesNo When Calling Another Macro
I have a workbook with a 2 macros "PopulateSheetlist" and "SaveEditedversion"

Normally they are each button operated, and prompt the user with vbYesNo style options. I wanted to write a Macro, say "Macroautomatewkbk that would automatically answer the prompts.


Sub Macroautomatewkbk ()

Call PopulateSheetlist

' Always answer YES or OK to any prompts this macro may offer

Call SaveEditedversion

' Answer No for the 1st prompt and YES for the 2nd prompt

End Sub

MsgBox "Both macros "PopulateSheetlist" and "SaveEditedversion"

' Some error handler here, don't know how this should work exactly.
Could any one please explain how to fill in code in the commented sections in the above sample code?

Edit: I tried recording a macro to do the above, but it only showed the zooming and scrolling that occurred, none of the button prompts being answered.


View Replies!   View Related
Pause Macro, Allow Format, Resume Macro
how to interrupt VBA code, allow user modifications to a chart, and then continue the code where it left off with a click of a button. I currently have the code in place where a UserForm pops up at a specific point and tells the user to make adjustments and press OK when done. It works fine but the UserForm displays and the macro resumes when OK is clicked, but I can't figure out how to activate the chart or the spreadsheet to allow editing while that UserForm is on the screen. If only I can get excel to allow me to sleect the spreadsheet and make changes while the UserForm is on the screen, I would be set. Can anyone please help me with any options to allow editing while the UserForm is on the screen so I can edit and then click the OK when done?

I posted something similar to this over two years ago. The way I got around it at the time was to break the code into two separate subs. This time around, the code is making 17 graphs and I want to ask for editing for each one before continuing. That would be a lot of individual sections of code.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved