As said a box appears with information guiding the user to what should be entered.
I inherited this spreadsheet and would like to edit the contents of this box and also learn how to make them myself.
Also I'd like to note that the box does not respond to right clicking
I found this spreadsheet on here and I have been trying to customize it to what I need. I am trying to have scores from skins match highlighted. I want only the minimum score to be highlighted but if there is another duplicate minimum score I don't want it to highlight anything. I also need to find a way to count the skins won by each player and have it off to the side.
For those not familiar with golf a Skin is a game where you try to get the lowest amount of strokes on a specific hole. Ex- 4 people play the hole one. P1 scores 4, P2 gets a 3, P3 and P4 get 6. The skin would go to P2 who has the lowest score on that hole.
Highlight lowest number in each column not highlighting if there are duplicates starting at L6 down to L11 and for each column till AC. And the same for the group just to the right on attached file.
On row 13 and 14 it tells me who won a skin. I want to tally up the total skins won by each player. so if Joe's names shows up twice on R14 I want it to tell me somewhere in the sheet Joe = 2
how to copy the contents of multiple comment boxes and paste in a single comment box.
The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.
I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.
I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
I would like a macro that prompts a user to title the first row of a worksheet. My coworker told me to use a message box with a defined input variable, but I have no idea how to do that.
View 14 Replies View RelatedI have a an Excel workbook (wb1) with 4 worksheets.
I also have a personal.xls that contains 4 worksheets.
wb1 has volatile functions.
I have calenders, dropdown lists, read from a file that is closed, etc..
When I open wb1, look at it (I do not make any changes by hand) then close it, it gives me the " Do you want to save?" prompt.
I have tried numerous codes to stop it and I can not get it.
I need it to give me the prompt if I have made a change and not give me the prompt if it is just a change from the volatile functions.
I cannot just tell it to save in the background because my boss does not want the date changed if it is just looked at.
The user opens numerous files when searching for a file that is almost what he needs, then he saves it with a new name and goes from there. Or he just opens numerous files to get ideas on how to make the new file.
The user cannot have to do anything to make it work.
I know that the code will need to be copied to any existing workbooks. I have the code to do that.
That would be done as the files are created to new ones. Also when he is starting from scratch the code would be in the template he uses. Eventually all of the files would be converted
Private Sub workbook_Open( )
Me.Saved = True
End Sub
Private Sub Workbook_Open()
ThisWorkbook.Saved = True
End Sub
I have an excel document which automatically runs a macro when it opens. The macro itself opens various excel documents, copies, Pastes and Deletes work sheets then saves the updated files in a new location. I simply used the Application. ScreenUpdating = False at the start of the macro and Application.ScreenUpdating = True at the end of the macro to hide all the various opening and closing and formatting of workbooks.
My aim is for the user to be able to open the document, wait a few moments while the macro runs, then be presented with the finished article when the macro ends without any need for interaction. The problem I am getting is as the macro runs, various windows pop up with messages such as "The file C:... already exists do you wish to replace the file" with a yes or no wchoice and "Data may exist in the sheet(s) selected for deletion. with a delete or cancel choice To Permanently delete the data press Delete". What I want to be able to do is stop these windows popping up and for the macro to go ahead with replacing the file or deleting the sheets without being manually told each time.
How do I highlight the word in B7 if I type a number or a letter in B7.
View 4 Replies View RelatedColumn A is all random dates . When the date happens on the 1st I need to highlight that row. I'm pretty sure conditional formatting is the way I need to go but have had no luck. The date format is dd/mm/yy.
View 4 Replies View RelatedWas working away on one of my spreadsheets and the black square that surrounds the cell you click on has vanished. i.e. if you click on cell A1, you normally get a black square surrounding the cell A1 to let you know that you are on it. That black square has disappeared.
It seems to affect any spread that I open but if I open a new spreadsheet it is there.
I work with many sheets that have many data columns and have used a "greenbar" formula in conditional formating but would like to have a similar formula that highlights the current row. If possible, it would be nice if this new formula would not wipe out all other formatting as it moves.
I have seen Ivan's work at www.mrexcel.com/hof001.php but it says that it wipes out other formatting.
The formula that I am currently using is =MOD(ROW(),3)=2
Is there a way of getting the current row number so that it could be
=ROW()=(current row number) ?
I have a problem regarding highlighting a specific cell, when i entered a specific date in another cell
Let me explain
If there is a Name, joining date, termination date/resignation date in cell A1 A2 & A3 respectively.
If the name is John, Joining date = 01/01/2012, Resignation Date 06/06/13
I need a formula which highlights the cell A2 plus all those cells which have the same date when i entered a date (i.e 01/01/2012) in another cell.
I have an appoinment calendar that is one month per worksheet
- Column headings 09:00 - 20:00
- Row headings 'days and dates of the month' (split into two merged cells of two)
****************See attachment 'Calendar Format.jpg'****************
I have then created a UserForm which has two dropdown combo boxes
****************See attachment 'UserForm.jpg'****************
What I want to do is have a way of using the dropdown boxes to select a column and row reference to enter the other data into the four lines in the outlined box area (see Calendar Format.jpg)
I want to be able to select a date - and to have only the dates from the sheet showing would be a bonus! - and have the script use that as its row reference, followed by selecting the time and having it add the column reference, and so selecting the correct cell to be able to then add all the other pieces of data.
I would just set it up so that it all worked by selecting the first cell, then adding, but I'm worried about other users not doing that and so causing things to be misplaced or overwritten!
I'm more than willing to change most things, but I do need to have those four pieces of info, and I only need to have a simple calendar.
If anything is not clear, please ask and I'll try to clarify further!
Forgot to add - In the pics I'm using 2007, but I want to be able to run this on 2000/2003 as that's what's in the offices and it's going to be shared on the server.
I need a quick code to where if a user attempts to open my workbook and they have any other workbooks open at the time that it prompts them with a yes no msgbox asking them to save before continuing. Really i just need the code to tell if there is another workbook open in excel.
View 3 Replies View RelatedI am not new to excel - but I have never required the use of advanced excel features before today - so I apologize in advance. I am a health professional student (graduate level) assisting my preceptor in developing patient management mechanisms.
I am building an excel spreadsheet to manage current medications for several patients.
I have built the spreadsheet with the first 'sheet' being a directory and subsequent sheets being patient data files(one sheet per patient).
I am now in the process of creating a macro to build new patient data files (a new sheet for a new patient). I recorded a macro to build the new sheet with the proper formatting of information. What I am trying to do now is automate the naming and linking of the new sheet.
What I am really after is how can you create a "prompt" such that after activating the macro button it asks:
Patient last name:
Patient first name:
Then uses this data to:
1. Rename the newly made sheet to 'last, first'
2. Title the newly made sheet with 'last, first'
3. Create an entry in the directory in the 'last, first' format
4. Finally, build the hyperlink from the directory to the newly created sheet
every time i choose Action X, i have a series of warning messageboxes appear. i want to run some code so that default option for each message box is selected every time a message box appears. this way i don't need to press Enter every time for each message box.
View 4 Replies View RelatedI have a workbook with extensive code of wich the VBE project is locked for viewing. Since i have distributed the workbook i had some users that gets prompt for a password when trying to run some code trough a button on a sheet. I have tried recreating this error by increasing the macro security level and unticking "trust access to vbe project" since i thought that is where the problem lies.
But i had no luck in recreating the error. I came accross some posts that had a similar error when you exit/close the workbook, wich was a problem with google desktop search being installed on the machine. Any ideas on why the user is being asked for a password ?, the code that should run does NOT try to alter/change anything in the code/project itself, so that shouldnt be the problem, but i can't think of anything else.
Workbook 1 has references to workbooks 2,3,4,5,6. When I click the top cross and choose Save All with Workbook 1 active, the deactivate event in workbook 6 runs to the end as it tries to close, but it can't close because workbook 1 references it. So nothing further happens until I click the top cross again. Then everything closes without any more prompts. Not good. If I could ensure workbook 1 closes first that should solve the problem. There may be another way of course.
I have attached 2 blank files to show the problem. test1 references test2. Open test1. It will open test2. Make a change to both files, activate test 1 and click the top cross, choose save all. Nothing happens usually.
how to highlight a particular cell red if the value in one cell is less than or equal to the value in another cell. For instance, How do I make it so that the "Hours to go" cell turns red if the value in the "Actual Last Completion" value is less than or equal to the value of "Last Completed" cell?
View 3 Replies View RelatedI have built a spreadsheet for my business which has an annoying feature I can't get rid of. When I click in a cell I want to enter data in the spreadsheet automatically highlights that cell and the neighbouring cell. I have to move away then back with the arrow keys to get back to the single cell I want to enter data in.
View 2 Replies View RelatedI have a macro, and it mainly works, except the second part where it checks if the value is less than 7, and returns it to a white background.
The cell has a formula in it that returns "" based on another series of cells. I have considered changing that to a zero and formating, but for reasons that don't need explanation, i have chosen not to.
here is the macro. I need the second part to be activated if less than 7, as well as if the cell returns ""
I have % figures in the following cells, E20, G20, I20, K20, L20, F24, H24, J24, G28, I28, H32, F16, J16, G12, I12, H8.
These figures are the result of a formula of other cells, with constantly changing values, therefore the % figures are constantly changing as well.
What I would like to do is highlight the cell with highest % figure, ideally to 'flash', either cell colour or flashing text.
What is the keyboard shortcut for highlighting cell? Can a frequently-performed task be assigned a keyboard shortcut by user? If it is done by VBA
View 4 Replies View RelatedI am getting a debugging error when I try to highlight the first row of a column that is sorted. the error occurs on the "if .autofiltermode then" line. the code does highlighting like I want but is giving me a run time error.
HTML Code:
Private Sub Worksheet_Calculate()
ColorAutoFilter
End Sub
[Code]....
I have a cover sheet to a workbook that holds hyperlinks to its relevent page. Several of the hyperlinks link to the same page but different relevant cells. However it is not overly clear which cell is active when you click the hyperlink. What I would like is for the active cell to be highlighted on the page. EG hyperlink 1 links to page 1 cell A2 & hyperlink 2 links to page 1 cell B6 - I would like A2 to be highlighted when accessed by hyperlink 1 & B6 highlighted when accessed by hyperlink 2.
View 9 Replies View Relatedin this sheet i have 3 groups of data. group 3 is the sum of group 2 - group 1.
how can i make the cells highlight that relate to the cell sum in group 3.
i.e when i click cell b38 cell b21 and b4 should highlight.
Have a spreadsheet shows 4000 warehouse locations (location of products). Each cell is a location in the warehouse (ba050, ca809, etc, actual warehouse locations, not grid ref); some products have more than one location, the locations are always next to each other. I import a text file into the workbook which tells me which product has more than one location (2, 10, 50, etc), that’s all I’m interested in ( multiple locations).
What I’ve done so far is to use vlookup to look at locations in file and allocated the number (the number of locations that product has) next (next column) to the cell that correspond to that location. What I need is a macro or formula that will look at the number and than highlight that many cells up or down the column, according to the number in the cell to the right (5 in cell, highlight 5 cells, 50 highlights 50). The warehouse runs up one aisle and then down the next and so on. So I need to be able to highlight cells up one column and then down the next and then up, then down and so on.
Multiple locations change on a daily bases, so need a quick way of updating 4000 locations.
New to excel don’t know if this is possible or not. Learning as I go.
Need some assistance.
Sample below.
Only interested if a number is returned, “******” can be ignored as will hide all columns expect ones with locations. So:
BA020 returned value of 4, so would like to highlight (any colour) BA020, BA029, BA040, BA049.
BB780 returned value of 2, so would like to highlight BB780, BB789.
POSSIBLE?
BA090 2 BA100 **** BB749 **** BB739 ****
BA079 **** BA089 **** BB760 **** BB750 ****
BA070 **** BA080 **** BB769 **** BB759 ****
BA059 **** BA069 **** BB780 2 BB770 ****
BA050 **** BA060 4 BB789 **** BB779 ****
BA039 **** BA049 **** BB800 **** BB790 ****
BA030 4 BA040 **** BB809 **** BB799 ****
BA019 **** BA029 **** BB820 **** BB810 ****
BA010 **** BA020 4 BB829 **** BB819 ****
I have some code that finds the appropriate string for a comment from another sheet. I then want to add the string to the appropriate range selection but am encountering an Object Variable or With Block Variable Not set on this section of
Range("C" & i).Select
ActiveCell.Comment.Delete <== Here Or the Next line
ActiveCell.AddComment (Commenta)
ActiveCell.Comment.Shape.TextFrame.AutoSize = True
Where Commenta is the string value I want to add.
How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..
(using excel 2010)
In wrote a workbook in Excel 2003 that is causing me a problem I can not seem to figure out. The workbook consists of multiple worksheets that work using both VBA code or cell formulas. The worksheets are protected (though w/o a password) to prevent users from accidently altering a cell formula. When the code needs to write to a cell, it first unprotects the sheet, writes to the cell(s), and then reprotects the sheet.
Everything seems to work fine, often for several months. Then somehow something goes wrong. Specifically, after double-clicking on the ListBox in the worksheet “CRI Calculator” to pick a drug name, it will improperly present a dialog box (see attached screen capture 'Unprotect Sheet DialogBox.JPG') prompting the user for a password to unprotect the worksheet. Again, no password has ever been set; I have tried entering nothing, “password”, and "anonymous". Regardless of how you respond, an error code “Run-time error ‘1004’: The password you supplied is not correct…” occurs. If I press Debug, it takes me (ironically) to the following line of code