I've got a couple of macros that are allowed to be executed ONLY when specific ranges/cells are selected. Is there a way to make a msg box pop up saying: "Wrong Area Selected" etc. if the User selects a prohibited range or cell and there tries to run the macros?
View 2 Repliesi have created a form in excel sheet1 (not a VBA form) and there are 10 cells with headings in the form which need to fill the data but i want to copy certain cells (suppose 6 different cells) from my form which is in sheet1 and paste to another sheet2 to certain columns horizontally.
View 1 Replies View RelatedEach week I run a report that produces over 2,000 rows. I have to manually go through and delete things, by the time I am done, there may only be 320 rows left. I would apply a recorded macro, but the report is not consistent each week. There may be more or fewer rows depending on the week.
I would like to be able to run a macro that looks in column B for a specific value and if it contains that specific value, I would like the cells to the right of it to have their contents cleared.
Next, in column C, I have...
Employee2's Name
Employee2's Number
SPACE
[Code]....
I would like to be able to delete the rows, starting from Employee 2 to just before the second SUB TOTAL.
So, all I would have left in column C is
SUB TOTAL
SPACE
I want to put a formula in a sheet.
Suppose In issue column there are few nos but until those nos will not be posted in Return column, those will not be posted again in another day's issue column, similarly until those will not be returned, those nos will not be posted in issue column. But for the first two days it will not work ad those are first time entries.
But this will be continued working on the next sheet for the net month.
28/11/09 29/11/09 30/11/09 Issue Nos. Return Nos. Issue Nos. Return Nos. Issue Nos. Return Nos. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
I need to create a warning when specific values sequences are input from a
drop down menu. My example:
I have 31 columns (days in month) and have 25 rows (peoples names) from the
drop down box I have the following values-These are shift designations
(V1,V2,V3,V4, SV1,SV2,SV3,SV4, A1,A2,A3,A4,N,R1,R2,R3,DB,C).
If the selections in cell between B1:AF25 are either SV 1-4 or A 1-4 I want
a warning to appear IF a value of V 1-4, R 1-3, DB or C are attempted to be
put in the immediately following cell.
Example: Cell B3 has A-4 and in cel C3 they attempt to put N, then it
should give a warning "This is not a recommended shift sequence".
This formula will only do 7 permutations, I need it to do 8. Is there some way to do that? Thanks in advance.
=IF(AND(A10,B10),B10,(IF(AND(A10,B11),B11,(IF(AND(A10,B12),B12,(IF(AND(A10,B13),B13,(IF(AND(A10,B14),B14,(IF(AND(A10,B15 ),B15,(IF(AND(A10,B16),B16)))))))))))))
Is there a way to have a calendar pop up when a particular cell is selected and only have Sunday dates as optional selections? Needs to be able to scroll by month.
View 8 Replies View RelatedI have excel file having macros and many formating , I have protected with password but in my office there is only 3 pcs having MS office installed out of 18 pc, all other pc have installed open office. formula and macro is not supporting in open office and also Macro which i have procted in excel easy viewing in open office without asking password (which i don't want )
I want to restrict the file to open in pc where open office installed.
In short excel file should not open other that pc where MS office is not installed.
I need creating a macro that i can use in a file that will open all workbooks in a specified folder. Each .xls file has a macro that is slightly different and i want to go to one book and run one macro that will make all macros run.
View 4 Replies View RelatedI have recorded (i.e. manually as opposed to writing VBA code) a number of macros to perform a routine, however they do not work when I change the filename.
Could someone please advise on how to edit these macros (which reference a specific filename) so that they work when the file name is changed. The macros copy and paste values from different worksheets and then run another set of macros. However all macros and worksheets are located within one excel file
I am having an issue with Excel creating a duplicate file when logged in as a user with restricted permissions on our network. If I modify a file while logged in as another user, excel will save the file but will also create a duplicate file with an eight character random name and no file extension. For example if user "Dave" opens a file called test.xls and saves it, in that same folder on the server a file named 296E9E20 will be created. Users are using Windows XP SP3 boxes and the server is running Win 2003. I am not sure why this only happens with Excel.
View 3 Replies View RelatedI have created a individual tracker spread sheet in each user system and on the spread sheet I have inserted 2 buttons in 1st sheet. when employee wants to take a break he has to click on the log out button and when he came back he needs to click on login button. I have another sheet named "timings" and I have created 2 columns, one is login and another is logout. What I need is when user click on the login button, the system time and date to be auto update under login column and when he clicks on logout button the system time and date to be auto update under logout column.
View 14 Replies View RelatedI am utilizing the following code to force the user of my database to enable macros. This is the
View 4 Replies View RelatedI have a VBA method that goes thru and calculates a bunch of stuff and puts this calculated info into a static data structure. This method is called 'setProjectInfo()'. This method is reliant on values on my worksheets, So as the user makes changes to the worksheet, I want setProjectInfo() to get called so it refreshes the data structure with updated information. I then have a bunch of helper methods to access different parts of this data structure. These helper methods are used in the formulae of a bunch of cells on a worksheet (ie '=getPercentComplete(period)', etc, etc).
This is all working well except for one thing. Sometimes the helper methods get called BEFORE setProjectInfo() gets called.. So, the helper methods return stale information. I can only get around this by manually recalculating the spreadsheet again. setProjectInfo() does enough stuff and takes a long enough time that I don't want to have to call it at the top of each helper method.
I've put the call to setProjectInfo() in a cell formula at the top of the my spreadsheet in hopes that Excel would know enough to call it first.. But that does not seem to work. Net, how can I get VBA to always call setProjectInfo() BEFORE it calls any other VBA methods?
I need to conduct keyword searches in excel and color the keywords a different color without changing the font color of the entire cell. now, i found some code from another thread that does this, but i cant seem to get it to work with more than 2 keywords. i need fine-tuning the provided code to do what i need it to do.
[URL]
the VBA code i used was this:
[Code].....
I was playing around with the example, the Cat_Mouse.xlsm, and when i modified the 'myList' and 'myColor' arrays, it does not work.
Cat_Mouse.xlsm
For example, I modified the code to add the word bat:
[Code] .....
When i run the macros, the word "Bat" does not become colored red. interestingly enough, when i substitute the word "bat" for the word "hat" in the "myLIST array, the word "hat" does change to the red font.
I am looking to use this code to address keywords in my excel file by coloring all key words red and i have more than 10 key words.
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View 14 Replies View RelatedI am attempting to use a named range as my Validation data source but have two questions:
1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range
2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.
I'm trying to use a conditional format on a range of cells so that if there is more than 1 "A" contained in any given row, the cells containing the "A"s are highlighted (e.g. red)
I've tried:
=COUNTIF(B2:K4,"A")>1
but it doesnt work & now I'm stuck.
Is it possible to only allow a macro to work in certain cell?
View 1 Replies View RelatedI have a list of authors for about 20 papers. These Papers are listed in sequential order from 1999 to 2005.
I want to track the number of similar authors from paper to paper. Thus I am attempting to create a macros that has the Papers listed sequentially when they were published and the authors for each paper, with the authors name in each cell. I want to create a macros that will compare the Author Cells associated to lets say the Fifth Paper with the Author Cells for the Fourth Paper (the Previous Paper). So that if certain authors come up in the Fifth Paper that were in the Fourth Paper, the # will be noted.
I also want this macros to compare all the Author Cells that have appeared in the past with that of the author cells of the (N) paper excluding the (N-1) Paper, and just note the # of occurences. So using the previous example. If we are talking about the Fifth Paper, it will look through Papers #1-#3 for any matching keywords and note the number.
I have attached my spreadsheet to make it more clearer. For some papers there are more than 20 authors as well just to note.
why I must update all cells, with formulas, manually after I do something with the workbook. I'll try to make an example.
If I make a macro that enters =sum("F1:F10") it says #NAME? when its done, but if I dubbleclick it and press enter it calculates the cell like its supposed to. I've tried to press alt + ctrl + F9, but that doesnt work.
Is there a command to dubbelclick the entire workbook and then press enter if you know what I mean?
A spreadsheet that can be used to enter the hours worked by staff each week and then calculate their Gross pay, Tax and Net Pay. THe basic spreadsheet has been created, however it will require aq macro to copy the Week ending, Hours worked, gross pay, tax and net pay columns to the first empty cell in row 1, clear the entries in the week ending and hours worked columns that have just been created and then hide the give columns to the left. which show the data for the previous week.
I know this sounds confusing but ive supplied the file to help you understand. The problem is that we can get the macro to hide the previous five entries and copy 5 new ones in their place but it only does this once. Just about our whole class is having trouble with this as we're not very advanced in macros.
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)
View 2 Replies View RelatedI have a custom function that is interferring with VBA. If I remove the function the VBA works fine, if I put it back to do what it needs to do, the VBA does absolutely nothing. Any ideas on how to make things work together? Here is the custom function and it is in a module where the VBA that isn't working is in the main Workbook page, but it is a Worksheet Change VBA affecting the same page as the custom function.
Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each Cell In Cells_To_Sum
If Cell.Rows.Hidden = False Then
If Cell.Columns.Hidden = False Then
total = total + Cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
I have a work book with several (couple of hundred) buttons, which when clicked display the contents of a range of cells in a message box as shown below;
View 4 Replies View RelatedI'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?