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

Userform To Fill In Blanks On Template Document And Save To PDF?

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

VBA - Cell - Fill Or No Fill?

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

Macro To Save File - Save Name From Cell Containing Date Using Different Date Formatting

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

Save As Cell Name And Then Save As PDF

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

Excel 2007 :: Conditionally Format Cell To Have The Fill Color Of Different Cell

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

Auto Fill Formula Every Nth Cell And Lookup Value Every Nth Cell In Source?

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

Fill Cell With Next Consecutive Number If Another Cell Has Data Entered?

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

Can I Fill Non-adjacent Cells With The Fill Feature? (example Listed)

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

Formula For Cell Fill Based On Data In Another Cell?

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

Changing Cell Fill Colour Based On Another Cell Value

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

Fill An Empty Cell With First Data Cell In The Same Column, Next Row(s) Down

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

Auto Fill After Inserting Row Without Using Fill Handle?

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

Auto Fill A Cell From Input From Another Cell

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

Mandatory Cell Fill In Before Moving To Next Cell

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

IF Statement (fill A Cell With Either 'YES' Or 'NO' Depending On The Value Of One Cell)

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

Automatically Fill Cell Value Based On Another Cell

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

Fill A Cell With A Formula Based On Another Cell

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

Fill The Corresponding Cell With Last Value Entered?

Jan 15, 2014

Fill the corresponding cell with last value entered?

View 9 Replies View Related

Sequential Fill Down Only One Cell?

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

Fill In Blanks With The Value Of The Cell Above

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

Fill-Down Until Next Blank Cell

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

Automate A Cell To Fill Itself In

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

Vba Fill In From Cell Above Over 2 Worksheets

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

Fill Cell With Data

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

Fill A Cell With A Color In VBA

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

Select Last Cell And Fill Down

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

Input Data Then Clear Cell But Save To Another Cell?

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

Create Directory From Cell & Save Workbook With Cell Value

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

Excel 2010 :: Save Open Worksheet To File And Include Date Of Save?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved