Adapt Current VBA Code To Make More Than One Cell Mandatory To Fill In Before Saving
Mar 30, 2013
I am currently trying to adapt the following code which is in VBA (Sheet1). It currently ensures mandatory cell entry of cell C2 before saving, and if this is not done then it would not save the item into the relevant directory. I would like to adapt this code so that cells B2 to R2 are all mandatory, and if they are not filled in a message box with the cell title (which would be in B1 to R1) would pop up. If the cells are not filled in then it will not save (the same function it carries out for cell c2 currently). The code is attached to a command button, i have tried to make the additional cells mandatory by playing around with the If Trim(.cells(2, 3).value) part of the code with the AND function etc however i have had not had any luck.
I trying to require data entry in Cell 2 if Cell 1 contains text. Normally Cell 1 is blank but once the user enters data (its a text field) I want Cell 2 to then become mandatory for data entry. (Cell 1 is actually C5 and Cell 2 is actually DK5. So formula I am thinking if is: If C5 contains data then DK5 must also contain data. If C5 is blank, the DK5 is also blank.
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
I have a piece of code (below) that autofilters a table based on a cell value (B7). I am looking to adapt this code so that the table can be filtered by an additional field in B8. This cell may be blank, in which case I want it to select all possible values (not just uncheck them all).
HTML Code:
Private Sub Worksheet_Change(ByVal Target As Range) If (Intersect(Target, Range("B7")) Is Nothing) _[code]....
I have a piece of code that prompts me to choose which column to be filled bottom up, I would like to modify this and add it in as a macro so that it fills columns H and columns I in a bottom-up fashion
This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it
Sub NextInvoice() Range("J2").Value = Range("J2").Value + 1 Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents End Sub Sub SaveInvWithNewName() Dim NewFN As Variant 'Copy Invoice to a new workbook
[code].....
I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.
My requirement is something like this. I have a workbook where end-users can fill data and send it to our system. They might enter any rows of data, it can be just 1 or even 100. I want to force users to fill in values in few mandatory fields : columns in B,C,D,E. Now I need a VBA code that should throw a message when it finds that the value in cells B,C,D,E are blank.
Since I am not sure how many rows of data the user can fill, I dont know how to code in VBA the above scenario. I cant give a range as the total rows of input data is not available and static value.
in my spreadsheet (attached), i want it so that when someone clicks a button, it will fill in the next available cell in column A. For example, when I click on the "Create" button on the green NIPRNet side, then it'll fill in the next available cell in column A (cell A4) with "(N) Created Account". Then, if I click on "Unlock" on the red SIPRNet Side, then it will fill in cell A5 with "(S) Unlocked Account". and so on, and so on, down column A.
At the moment, i have it so that all the cells in column A are dropdown menus. But I want to eliminate the drop-down menus and use buttons instead.
I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code: Sub Test() Dim Rng As Range Dim wb As Workbook Set Rng = Range("A1:A2") Set wb = Workbooks.Add With wb Rng.Copy
if I have one cell that ive enlarged to half the sheet.... and it is effected with rollover hyperlink so that when I hover over other cells. it turns different colours..
is it possible to make that cell open up different files using the double click event depending on the colour it currently is?
i.e. if its blue, open file1. if its yellow, open file 2 etc
I have 4 columns, If column B (Ref No) is filled in, the adjacent columns, C,D and E become mandatory and an input box pops up on screen one after the other for entry into each column.
My problem is that I require a drop down list and not a input box for the final column (status). So i have created a userform with a combobox dropdown. I am having problems connecting the combobox selection to the final column, and for the selected combobox item to go into the right cell like the input box entry currently does.
Attachment 53209test743.xls
I have attached a sheet and also you can see the code below.
Code in Sheet
VB: Public SaveVal1 Public SaveVal2 Public SaveVal3
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
I am trying to create a code so that when the save button is hit, it puts the current date and time into a given cell before it saves. This way I will know the last time something was updated and saved.
I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:
I needed a code that would input the current date and time in the cells in column N whenever changes were made to any cells in the row from columns A to M. For example, if I change a name in cell 6D, then cell 6N would automatically change to the current date and time.
I found a useful code on a forum (maybe here, don't know for certain) and modified it to suit my needs (see below). I am however now getting a debugging error suggesting that the second line that reads "Private Sub Worksheet_Calculate()" is causing an error.
If input is entered into column C (range C2:C100), then row cells for columns D, E ,F, H or I must NOT be blank upon save. Could be that one or more of these row cells are left blank by mistake.
An error msg pops up upon attempting to save, stops the save and colors each cell yellow that needs info entered into.
Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.
How to "hard code" a cell's fill color? I want to assign a cell's fill color to yellow. While setting the fill color is easily done, my problem is that as soon as I paste data into that cell, it looses its fill color. Is there a way to tell the cell to never change the fill color?
2 Questions here...(Entire macro is at the bottom)
QUESTION 1 - In line 4 of the code below, in Column B, how can I get this code to auto-fill ONLY equal to the bottom-most row with any data in column A? Note that it's never exactly "B254"
QUESTION 2 - How can I change these 3 lines of code...
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then Cancel = True
I have a project coming up where I will receive tables in a certain format. I want to be able to create a macro that well create a chart based on a single inputted cell (start of data). The chart will have 4 series and five values per series. See the link below for sample data! The first data point is not in the same place in the sheet, but the table will have the rest of the data relative to that!
how to make a date automatically show up in the current year.
For example, if A1 = 6/2/2005, and I want B1 to equal that same month and day, but 2011... How can I do that?
I know =6/2/2005 + 365*6 would work, but the date in A1 will vary... so it has to be a way of showing ANY date with ANY year with the same month/day but current year.
So i got a few macros on a worksheet and one of them is simply :
Sub Pr_Removefilter() ActiveSheet.ShowAllData End Sub
Ive worked on the excel a couple of times, everything fine. Then it started crashing upon saving (after changes made that had no possible connection to the macro, on another sheet). After a few painfull trials and errors i located the problem to be this macro button. I erased it, created a new one with the EXACT macro and it works. Why?..
We have spreadsheets which record our sales team's prospects and orders. In order for the sales director's summary spreadsheet to be correct, the month which the order came in has to be filled in and correct.
What I would like to be able to do is when column K of a line is turned to "100 - Purchase Order In", a message to pop up saying something along the lines of "Is the Month In correct?"
Looking through previous answers, I think that this is something along the lines of what I need:
Sub Print_Out()
With Range("d6") If .Value = "" Then .Select MsgBox ("Make sure you enter your surname") Exit Sub End If End With ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub
However, I don't know enough about VB Code to know how to change it for my purposes...
