I want to be able to specify inside the message the word that is in cell F6. So it would say "You Must Give A Reason For The Amount Of Mgr Voids For Shawn"... shawn being the name in cell F6.
If Range("F9").Value > 50 Then
MyInput = Application.InputBox("You Must Give A Reason For The Amount Of Mgr Voids For This Employee")
If MyInput = "" Then End
If MyInput = False Then End
ActiveSheet. Unprotect ("13792468")
ActiveSheet.Range("F9").AddComment
Range("F9").Comment.Visible = False
Range("F9").Comment.Text Text:="" & Chr(10) & (MyInput) & Chr(10) & ""
ActiveSheet.Protect ("13792468")
End If
I have the following code that allows a user to type in an email address into an inputbox, and email a spreadsheet to the recipient in the input box. However, as 99% of the time this is going to be the same email address every time, can I populate the inputbox automatically with a given email address? for eg email@email.com
vaRecipients = Application.InputBox("Please enter recipient's email address. Please ensure Lotus Notes is open before sending.", "Email Literature Request")
Sub AddRows() Dim r As Long For r = Cells(Rows.Count, 6).End(xlUp).Row To 2 Step -1 If Cells(r, 6) <> Cells(r - 1, 6) Then Rows(r).Insert Shift:=xlDown Next r End Sub
but find it somewhat unflexible. I don't always use the same column reference to determine where to add the rows. I was hoping someone would be able to integrate the inputbox usage that is in this delete rows code I got here
'Get the relative column number where the criteria should be found lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _ & "the criteria can be found.", Title:="CONDITIONAL ROW DELETION COLUMN NUMBER", Type:=1)
I am wanting to run an insert row macro and it prompt me asking what column I want to reference. Whenever there is a change in that column, insert a row.
I wanted a macro to insert (the number of rows from Input box) below the active cell. Searching the forums, I found the following code.
Sub InsertRows() Dim Rng Rng = InputBox("Enter number of rows required.") Range(ActiveCell.Offset(1 0), ActiveCell.Offset(Rng, 0)).Select Selection.EntireRow.Insert End Sub
If I enter a number it works fine, but if you click on the cancel button or the X to close the Input box, I get a Run-time error (13: Type mismatch). If you enter zero, it inserts 2 rows above the active cell.
I tried to modify another macro, I found on the site, that inserts rows and copies the row above, but always get the Message Box "You didn't specify a range!"
Sub InsertNumRows() ' Dim Rng As Range
Application.DisplayAlerts = False On Error Resume Next Rng = InputBox("Enter number of rows required.") On Error Goto 0 Application.DisplayAlerts = True.............................
I have the code below. That code call some InputBoxes in sequence, that be filled with correctly information like name, cell phone, date of purchase, etc. (sheet is in Portuguese)
But I want to give a function for 'Cancel' Button, because actually if we click on cancel Button, the macro skip to the next inputbox.
I want to click in Cancel Button, and Exit Sub, I used this Tip for example
[Code] ....
But if we do not fill the Text field, the Macro Exit Sub Too
Some fields are optional, so for this i search for a solution
Is there a quick Macro I could use to rearrange text in a cell. For example our organisation list all the departments in an unconsistant way and it makes finding the departments hard using A-Z. For example some department will say "Department of ...", "Dept of ...", Division of ..." or "Div of ...". (Obviously excluding the quotes).
What I wanted to know is this. If you highlight the range you wish to work with, could you use a message box to ask you what text you wish to edit and then automatically search the range and make the changes.
For example search for "Dept of " (note I would need to keep the space after of otherwise I would have leading spaces) so that "Dept of ABC" becomes: "ABC, Dept of" and say "Division of XYZ" becomes "XYZ, Division of"
In a range of cells (e.g. B26-B40) I have names (first name and second name or first initial and second name). This data is carried from sheet 1 to the second sheet via formula. Some cells may be blank as well.
A VB code to display a message when the user changes the name in any of these cells with data or adds a new name to a cell that is blank, in sheet2
I have the inputbox so i can set a string value, When the inputbox Cancle button is pressed i want to exit sub, If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname. If the inputbox has a value do >>>>>>That>>>>>
I have a macro that takes input from the user and replaces certain text on several worksheets. One of the inputs is a username in the format of firstname.lastname. I need to manipulate this input such that the dot is removed and the first twelve characters only are used, all in upper case. e.g. Michael.Jackson would become MICHAELJACKS
I know how to use cells.replace to replace the text but I don't know how to use a formula to manipulate the inputbox text before I do the replace.
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.
My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.
Here is the code I presently have for this part of my macro.
Sub GetOpenFileName() strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")
If strFind = vbNullString Then Exit Sub
With Sheets(strSheetsMainCompProfile) If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then MsgBox strFind & " cannot be found on this sheet" Else
I have an excel file with many worksheets. I want to get a pop up message providing definitions of different subjects when they are entered from a drop down list in a specific range within a column to improve the users understanding of the subjects.
Worksheet 1 Range (where I want the pop up message to be valid): J85:J385 Subjects from drop down list in specified range: "x,y,z" Pop up message: "Definitions of x,y,z"
Do I have to make a new module, or write the macro in the selected worksheet? What should be the settings of the macro (general, worksheet, declarations etc.)
I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.
For example:
- All of my data is in column A
-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.
I need a nudge in the right direction with how to amend the below code so that it : 1: cycles though all cells in a workbook, and sees whether the text reference of the cell contains a picture filename (i.e. searches for .png or .jpg in the cell contents) 3: if (1) is correct, it retrieves the picture from "C:/Users/jeff/Documents/Standards/" and pastes it as a cell comment background in the cell to the right (and overwrites any comment backgrounds that might already exist there)
The code below does something a bit different: it looks in a defined range, then adds a comment with a background picture retrieved based on the text in the cell to the left.
I'm sure this is a pretty basic change, but my VBA skills aren't up to it...I've only just started reading though Walkenbach's Power Programming! I'm using Excel 2007
Sub InsertComment() 'www.contextures.comxlcomments03.html Dim rngList As Range Dim c As Range Dim cmt As Comment Dim strPic As String
On Error Resume Next
Set rngList = Range("A1:A5") strPic = "C:/Users/jeff/Documents/Standards/" On Error Resume Next For Each c In rngList With c.Offset(0, 1)...................
I have a workbook with single sheet called Fronpage. I want to insert a new sheet and rename the sheet based on the character value user specified in R1C1 in the Frontpage, and also would like the new sheet to be the last sheet in the workbook. I have tried to use DDE, and also Macro recording to do it. It didnt work out. Can anyone have any code to do it ?
1. search A1:AZ1 to find the cell that has the text "VBA Test" in the cell. There could be other text in the cell as well - this is not an exact match - but these two words are the common text.
I'm looking for a quick and easy way to insert a value in the first empty cell in a multi-column, multi-row range using VBA. I suppose I could loop through the range, but the table could grow to immense proportions and I don't want to slow everything down.
For example, the new value should be placed in cell C4. It doesn't matter whether the range is looped through the rows or columns, either will work just fine.
I am trying to write a Macro that will insert a Text Box that auto-fits the shape of a cell to hide its content. Once finished, the Macro will need to lock the cell and the text box so the contents of the cell are hidden. The idea is that I want to share a spreadsheet with someone but want to hide individual cells for various reason.
I am working on a production spreadsheet, and I'm trying to figure out the easiest way for my coworkers to enter data into the system. Each shift, we produce from 5-20 different varieties of chips, and we keep track of how much we've made of each. I've decided that a simple ADD and DEL button at the end of each row will work (unless anyone can think of something easier or better, let me know pls).
Cell G3:G30 is where data will be stored for each variety. The default value for each variety is stored in column C. I found a very basic macro that adds a value to a selected cell, but I wish for it to find the default quantity for the variety in that row (changes day to day as per our schedule), and add it to the value in column G. The DEL button will do just the opposite.
I have 3 worksheets that I need this macro in as well (days, afternoons, midnights), but I don't think that will be any problem. I've searched around for a solution, to no avail.
I am using the first part of the code to select the last cell in a column. I wish to enter that cell into a InputBox, rather that the default "A3" shown below. How do I modify the second part of the code?
Dim strName As String Sheets("Prices").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Set wSheetStart = ActiveSheet strName = InputBox(Prompt:="Type in column A cell to paste component into", _ Title:="Price List Paste", Default:="A3") Range(strName).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False
I have made a macro to nominate a % rate in an input box , then have a value multiplied by this rate. Now i need the macro to place the rate followed by a '%' symbol in the cell below.
I'm working on a tracking spreadsheet that needs to be able to function on both Excel 2004 for Mac as well as Excel 2007 for PC. The code below is written to ask for a date when the status of a particular row of cells is changed to a value other than "". These cells have validation lists where the list itself is just below the cell in hidden rows. It then places the date entered in the corresponding cell to the right of the list choice in the hidden rows and the top row is simply a vlookup to show the date with the status choice.
This way, when the status cell is changed again, it retains the date in the hidden rows while setting a different one. The code below is obviously not the full code, the whole code includes other functions and is a bit long, but I've narrowed it down to this:
I have the following code in Destination.xlsm that is intended to:
request the user to select a multi-cell range in a column of single-sheet Source.xlsm, in which some but not all cells contain "Y" (to indicate that this row of data relates to a National Account, versus a Territory Account)loop through the selected range, and whenever a cell contains "Y", copy the entire row and insert it in Destination.xlsm above a cell named "rngDest".
Code:
Sub Copy_NationalAccounts_Rows()
Const Message As String = "Select the entire range containing National Account Y flags, then press Enter or click OK." Const Title As String = "Copy National Accounts"
Dim rngSource As Range Dim rngDest As Range Dim r As Range Dim c As Integer Dim wbk As Workbook
I'm trying to setup vb message box code on a group on cells so it looks at cells that doesn't belongs to 'ortho'. I want the message box to appears when it's not 'ortho'.
Cells between A1 to A10 will show ortho, but if one of those cells contains a different text when entered (a human error), this is where a would like the message box to appear saying something like 'Title name "Error Group"; 'message saying "this person is not part of this group"
I can't seem to make user-defined format that puts a text in front of a number and/or a text.
Let's say I have A1: 13, A2: texttext A3: text7 and I want to format a lot of cells to "Ilike 13" / "Ilike texttext" / "Ilike text7"... ie add the same text in the front of the cell, no matter what the content is.
I did manage it seperately, with "texttext" @ for text and "texttext" # for numbers, but what's the general one?
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
