Insert Text From Cell Into Inputbox Message
Apr 21, 2006
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
View 3 Replies
ADVERTISEMENT
Jan 13, 2012
I'm trying to write a bit of to take a text string collected from an inputbox, and paste it into a specific cell.
Should be easy but where my text string from the inputbox is "XYZ", when it enters it into the required cell it enters it as " ="XYZ" ".
Attempt at code is below -
Code:
Sub EmailEdit()
Dim Response As String
Response = Application.InputBox("Input administrator email address", , , , , , , 2)
'Check to see if Cancel was pressed.
If Response = "" Then
[Code] .....
View 2 Replies
View Related
Aug 29, 2007
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")
View 2 Replies
View Related
May 11, 2007
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.
View 4 Replies
View Related
Mar 12, 2008
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.............................
View 9 Replies
View Related
May 13, 2014
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
Click in Cancel Button and Exit Sub
[Code] ....
View 5 Replies
View Related
May 13, 2009
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"
View 4 Replies
View Related
May 16, 2006
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
View 3 Replies
View Related
Jun 30, 2009
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>>>>>
View 6 Replies
View Related
Jun 3, 2009
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.
View 4 Replies
View Related
Oct 1, 2008
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
View 9 Replies
View Related
Jul 10, 2013
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.)
View 7 Replies
View Related
Feb 20, 2008
I'm trying to write a macro similar to the one found here: here:http://www.techonthenet.com/excel/macros/checkbox.php.
Rather then use a textbox, I would like the cell to display the date when text is inserted in the cell to the left.
ie. I insert text (the letter 'a') in cell E11, and the date appears in cell F11.
I would also like the macro to do this for a range of cells ie. for E11 to F21, then from G11 to H21, then from I11 to J21, all the way to column IV.
View 9 Replies
View Related
Aug 15, 2014
With "sheet1" there's a table c14:m38 with all formulas. But i want to expand the table if cell c38 contains a value or text.
Is it possible to automaticly insert a row containing the above formulas in row c14:m14?
View 12 Replies
View Related
Aug 8, 2014
Is it possible to insert text an a cell value in conditional formating, i,e Ive got the conditional formatting:
=AND(H$4>=$B5;H$4<=$C5)
I want to insert this text whenever this condition is true once and not to repeay it:
="Load " &TEXT(G$5;"dd-mmmm") -- where G$5 is a vallue cell_
View 1 Replies
View Related
Oct 19, 2009
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.
View 5 Replies
View Related
May 10, 2009
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)...................
View 9 Replies
View Related
Dec 14, 2006
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 ?
View 2 Replies
View Related
Jun 11, 2008
I want the macro to:
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.
2. go to that cell
3. go to one cell below that
4. enter a formula (I've got it from here ....)
View 4 Replies
View Related
Nov 20, 2013
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.
A
B
C
1
5
65
56
2
32
12
89
[code]...
View 1 Replies
View Related
Sep 11, 2009
I built a formula that should work, but it's too long so I need to condense it.
I have three columns, column 1 has names, column 2 has a formula.
I have 15 sheets, each with a name that could appear in column 1.
If the cell in column 2, sheet 1 is Bob, I want it to pull H5 from sheet bob. That works as:
=IF(A5="Bob", 'Bob Data'!H4, "Work in Progress")
But if I build that formula for all the possible names, it's too long. Is there a way to make the formula autofill with the name in cell A5
So: =IF(A5="XXXX", 'XXXX'!H4, "Work in Progress")
View 13 Replies
View Related
Nov 30, 2011
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.
Sheet1A1SAMPLE DATA2sample3sample4HIDDEN5sampleExcel 2007
I tried to record a macro as a starting point but it recorded nothing. I searched around and it seems to be an issue without a solution.
I could obviously change the formatting and the contents of the cell but the idea is to preserve the contents if possible.
View 4 Replies
View Related
Oct 2, 2008
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.
View 6 Replies
View Related
Apr 23, 2007
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
View 7 Replies
View Related
Nov 21, 2007
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.
View 9 Replies
View Related
Nov 20, 2009
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:
View 5 Replies
View Related
Jun 15, 2012
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
[Code] ........
View 5 Replies
View Related
Dec 3, 2007
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"
View 9 Replies
View Related
Sep 24, 2008
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?
View 12 Replies
View Related
Mar 23, 2012
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;
View 2 Replies
View Related
