Fill In A Cell Before Save
Dec 4, 2009
I am creating a form for others to fill in. But they don't fill it completely.
So i want to force them to
If they fill in Cell G31 or H31 they must also fill in cel F31, before it will save the document.
If they try to save the file without F31 filled, it should give a messagebox that
says: Fill in Volume.
Privatesub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Dim msg As String
If Not Worksheets("Sheet1"). Range("G31").Value Like "##" then
msg = "Befor saving, fill in Volume."
Cancel = True
End If
If Not Worksheets("Sheet1").Range("H31").Value Like "##" Then
msg = msg & vbCrLf & "Before saving, fill in Volume."
Cancel = True
End If
If Cancel Then
MsgBox msg
End If
End Sub
View 9 Replies
ADVERTISEMENT
Apr 4, 2014
I have a word document that is a rental agreement. Im trying to make an easy to use user form where a person can input the information and it populates in the correct areas and saves as a pdf. How I could put the word document into excel and have a userform populate designated areas.
View 1 Replies
View Related
Jul 13, 2012
I'm trying to loop through a list of about 1300 items and copy the rows where one of the cells has a fill color to 2 different sheets in the same workbook. If there's no fill color, the row moves to a different sheet.
My code is determining that all cells have a fill.
For Each cell In Range("Classification")
If cell.Interior.Color blank Then
cell.Select
Range(Selection, Selection.Offset(0, -9)).Copy Destination:=Range("Compliance")
Range(Selection.Offset(0, -5), Selection.Offset(0, -9)).Copy Destination:=Range("Mstar")
Else
cell.Select
Range(Selection, Selection.Offset(0, -9)).Copy Destination:=Range("RP")
End If
Next cell
View 3 Replies
View Related
Aug 15, 2014
I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.
The format of B5 looks like - 13/08/2014 16:39
The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".
The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14
I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.
[Code] ......
View 3 Replies
View Related
May 31, 2012
I have a VB set up that "Save As" the SHEET, with name based on the contents of a particular cell. - which works perfectly.
I then have another VB setup to save new file as a PDF.
The issue I'm coming across is that it is saving the PDF as the original excel workbook's name, not the new "Save As" name
example
Sample.xls has contents in Cell A1, when Macro activated it saves the active sheet as a new file with the contents of A1 as its name. Lets say Bob.xls
Sample.XLS also has another Macro, which when activated, saves as a PDF.
This macro is carried over to the newly created bob.xls
Issue is, it is saving bob.xls's PDF, as sample.pdf.
Anyway I can get it to save as bob.pdf without having to create a new macro every time?
View 4 Replies
View Related
May 25, 2011
I have a spread sheet and I want to conditionally format rows to be a certain color. That part I'm fine with. But I don't want them to be a set color. I have a "key" of different colored cells that I want to be the fill colors of the formatting. The ultimate goal is that for example the key looked like this
red
blue
yellow
green
then the rows I had would be formatted as red, blue, yellow, and green. But if you were to go into the key and change the first cell from red to purple, then the rows would become formatted as purple, blue, yellow, and green. Obviously I can copy formating by hand using the format painter, but I want it to update automatically.
View 4 Replies
View Related
Dec 19, 2013
Source tab contains vital information about some clients.
In the aggregated tab (Cell C10) I created a formula that pulls the Inflows from the source in a very specific array. So for client 1, this works fine. Now, if i copy my formula to the client 2 (Cell C14), it obviously wont go and look in the correct array in my source.
What i need to do is to be able to copy/paste my formula
[Code].....
(from cells C10 to CC10) to cells C14 to CC14, but when copied, the look up array changes to:
Formula: [Code] ....
I will have to fill this formula to at least 100 entries down, so i need to make it work with ease
The good thing is that all look up values in the source increase by a fixed number of rows (12). I tried playing with index/rows formula.. no luck..
Attached File : samplev1.xlsx
View 1 Replies
View Related
Aug 16, 2012
I need to achieve the situation below with a formula. I have tried simply doing, for example, = B3+1 but this throws an error, probably because the value being checked is alphanumeric.
Data
A001
Data
A002
Add next number (A003) when data entered in cell to left
View 2 Replies
View Related
Nov 7, 2008
how to enter data in non-adjacent cells using a fill command.
Here is what I am trying to do:
in the column, I am holding ctrl button to select every 10th cell down the sheet. I need to enter a date in every selected cell that is exactly 7 days apart, i.e., 11/7/08 then 11/14/08 then 11/21/08, etc.... but no other dates or data.
I have tried to figure out a way to do this other than manually, but am confounded.
View 9 Replies
View Related
Dec 20, 2013
How do I write this formulae ??
For cell range M6:AV6 fill with N/R If the data in cell D1 = Wont have
View 5 Replies
View Related
Oct 2, 2008
I have a table:......
I want to colour in the relevant cells for each ref depending on the numbers in the stage column. So for the first line, the cells in columns 1,2,4&6 need to be shaded in. For the second line, the cells in columns 1,2,3,4,&5 need to be shaded in. I already have a lot of conditional formatting set on the sheet so need to do this with a macro.
I have tried to write something but am having issues with it finding the 1 and shading in the correct column but then jumping out of the loop and on to the next cell.
View 4 Replies
View Related
Sep 19, 2009
I am looking for advice on how to move cells containing data (columns D to K in EXAMPLE A), upwards so that the information lines up with data already existing in columns A to C (to be included as part of an overall VBA routine).
In the upper example data in cell D2 needs to move upto D1, E3 to E1, F4 to F1, G7 to G1 etc and K9 to K1. Now, data on following rows is to move upto row 2 eg: E5 to E2, F6 to F2, G8 to G2.
The first 'block' of data starts at row 1 and finishes in this case at row 9.
The next 'block' of data starts at row 10 and finishes at row 18.
Row 19 shown is the start of the next 'block'. These 'blocks' may have upto 20 rows ....
View 11 Replies
View Related
Apr 30, 2014
Is there anyway to automatically fill the empty added cell after inserting a row without using the fill handle? For example, for a series of numbers: [URL]
2. Drag the fill handle Selected cell with fill handle across the range that you want to fill.
Or running balance: [URL]
2. Extend the running balance formula into the new rows by selecting the last cell in the balance column and then double-clicking the fill handle.
View 6 Replies
View Related
Sep 6, 2007
I would like to auto populate a cell (A2) due to the input results from another (A1).
For example, if I enter A1 with "100%" then I want A2 to auto populate with "Complete". And just the same for other percentages; if A1 was input with any range from 1% to 99% it would populate A2 with "In Progress". And if A1 was to equal 0% then A2 would be "Not Started".
View 10 Replies
View Related
Mar 8, 2013
I have a worksheet with running macros which hides & unhide rows based on input of data in yellow highlighted cells (see attached). I'm trying to make all cells mandatory. The below code works but I run into problems when certain cells made mandatory are hidden. For example if I select "Expense" as Type of Expenditure in the attachment cell C10 & C11 are hidden so if its mandatory I won't be able to move forward to next cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Me.Unprotect "dawnwh81"
Dim myCell As Range
Dim myRange As Range
[Code]....
View 2 Replies
View Related
Oct 10, 2008
i'm trying to ask my spreadsheet to fill a cell with either 'YES' or 'NO' depending on the value of one cell. I've succeeded in getting it to enter 'YES' but can't figure out how to tell it to choose between the two options. This is the formula so far
=IF(L5>2,"YES")
View 3 Replies
View Related
Dec 2, 2008
I have certain values in cell B4 to B2000. There are 25 unique values in those cells. In cells A4 to A2000 I would like to put a identifier (lets say a letter from A to Y) based on the adjacent B4 to B2000 cell values. I can't use a IF function because I am way over 7 allowed in excel. I could use a vlookup but that seems to slow down the file quite a bit. Is there something else better/faster out there?
View 3 Replies
View Related
Mar 15, 2007
I need to be able to fill in the day automatically when a date in entered
c1 (i type in) 15/3/07
then in a1 the day i listed by the formula =c1 and formatted to "ddd"
so i only type in the date and it fills in the day cell automatically down the rows are i enter new data
View 9 Replies
View Related
Jan 15, 2014
Fill the corresponding cell with last value entered?
View 9 Replies
View Related
Jul 20, 2014
I'm making a Purchase Order generator for work. Essentially, the main screen has buttons and the user selects the company, job number, their name etc. They click 'Generate' and it will great a brand new excel file for them with all the correct codes, ready to populate and send to a client. In the main sheet, we also have a master list showing every purchase order made to date. This is where I am currently stuck. I will have many more questions on the way. This is my first program so very new!
Lets say we have the following in cells A1 and A2. The rest is blank:
REQ0001
REQ0002
[Code] .......
View 3 Replies
View Related
Sep 17, 2008
I have a list of product codes. Some of the cells in this list are blank, and it is assumed that the blank row is the same product code as the one above it. Is there a formula I could use to have the cells fill themselves in?
Example
DO-566
DO-566
<Blank Cell>
DG-233
<Blank Cell>
<Blank Cell>
..........
I've tried the following, but it only yeilded each of the cells having the same values. =if(isblank(A2),A1,A2). With this, I allowed circular references, but it still yeilds that same result stated above.
View 4 Replies
View Related
Apr 5, 2013
I have lot of data in column B. I need to copy the first cell and paste content to next ones before empty cell.
Replacing xxx with the data in first cell all the way to the end of column B.
Before:
After :
123-1
123-1
xxx
123-1
[Code] ........
View 4 Replies
View Related
May 21, 2013
I have a spreadsheet with alot of information about a product. One column, is used to fill in a price that the item is going to be and in the column next to it, I show the date this change will happen.
What I need is another column (that has a price written on it already) to change and put in the price found in the "new price" column, if the date is equal to the column which show the date the price will change.
Example:
A
Item
B
Item's current Price
L
Item's increased price
M
Date increase will happen
I want "B" to change and reflect what "L" is reading when the date is the same as what is entered in "M".
Can that be done?
View 2 Replies
View Related
Jan 14, 2010
I'm looking at filling in a cell value from the cell above in column A but over a range of worksheets. It only needs to fill down until the last used row.
The last used row in the worksheet will vary for each worksheet .. but the longest column will be column T or U.
this code "works" but only for the active sheet... i'm sure its something easy to adjust.
Sub CopyDataDown()
' ---
Dim ws As Worksheet
Dim Lr As Long
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name "Notes" _
And ws.Name "FrontSheet" Then
Lr = WorksheetFunction.Max(Range("T" & Rows.Count).End(xlUp).Row, Range("U" & Rows.Count).End(xlUp).Row)
With Range("A2:A" & Lr)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Jul 17, 2006
on a form i have a combo box and on that combo box change event i want the following to happen. If the cboBox value = a company name then search database till you find that company name and then insert the companys details into a specific cell. So is that possible. im sure it is. I think it needs to be done by first checking the value of the box and then searching the database for that value. and lastly telling the cell it needs to be inserted into.
View 4 Replies
View Related
Aug 24, 2006
I have a question, can I fill a cell with a color in VBA? If is that possible, how can I do it?
View 2 Replies
View Related
Apr 3, 2007
I want to select the cell under the last cell of a particular worksheet and use the filldown function. This is the code i am tryin to use but won't work
Worksheets("CD").Range("A1").End(xlDown).Row + 1.Select
Selection.FillDown
View 4 Replies
View Related
Jan 29, 2014
I want to be able to see the average rating for each employee.. Is there a way to put in the rating from one survey and have it automatically add it another cell and then have it clear the first cell?
Here is an example of what I would like to do: Say we get 2 surveys with one rating of 5 and one rating of 10... If I input the 5 into cell Q4 and have it automatically put into cell R4 and then it gets deleted from Q4 but saved to R4... and then I can add the rating of 10 to Q4 and it automatically ads it to R4 and clears Q4 and totals R4 to an average of 7.5.
This way, with each new survey I can just input each rating to Q4 and have it cleared for the next rating and then have an average rating of all the surveys in R4... we do not need to keep track of how many ratings we are getting, just the average rating.
I have attached the excel sheet if you would like to take a look at what I am trying to do..
View 1 Replies
View Related
Dec 3, 2006
what code can be used to be place in a Command Button on a worksheet that will use the value in a cell, change it to the directory where it have to be saved and save it to the value of another cell. Example:
On a worksheet called "Customer", at cel A1 will be the customer's name and at cell A2 will be the asset number. Now let's say a directory does not yet exist on the root for this customer, a new directory must be created in the root directory by using the name in A1, and the workbook must then be copied and saved under the value of A2.
View 6 Replies
View Related
Mar 11, 2014
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
View 6 Replies
View Related