Which function would I use if I wanted a macro to run upon the user either clicking on a cell within a certain column, or tabbing to a certain column.
Right now I am using
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
But it doesn't take effect until after the user has inputted something into the cell.
Its been a while since I did this on 2003 and needing a pointer on 2007.
Just needing a simple macro that opens the relevant folder from a hardcoded path to allow the user to manually select the file to be used.
This file will then be used to copy from and paste to another file.
I tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:
1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.
I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................
Is there a way for VBA to prompt the user for a new selection?.
For exmaple,
Code:
Sub BlankRow()
The reason I need this is because I am already in a macro and can not change selection on screen and I don't want to exit routine to make new selection (last line of code).
'I need to be able to select new active cell!
ActiveCell.EntireRow.Select
Selection.ClearContents
ActiveCell.EntireRow.Interior.ColorIndex = 1
ActiveCell.Offset(1, 0).Select
Edit.Show
End Sub
I have a macro that locks the cell (cell rangeis given) after entering data into cell & thereafter if we have tochange the value of cell it asks for password. It works well.
I want somechanges in this macro - currently if I move cursor on locked cell then alsoit prompt for password & i want if i edit that cell by double click or F2then only he ask for password.
I have a sheet that has vast amount of rows and all I need is for a message prompt to appear if there is an empty cell in columns A to E and if possible the blank cells reference number to be displayed in the message prompt?
View 9 Replies View RelatedI am trying to password protect user input on a specific text entry, for example if a user tries to enter the letter A they are prompted they require a password (I am trying to do this for authorisation.).
View 3 Replies View Relatedwhen the user tries to enter data into Ranges E25:E26, G25:G26 OR I25:I26 I want to have my own customised prompt that says: "You are using Imperial Units. Enter drafts in the Imperial range by clicking the Imperial button on the left".
To make it more interesting, the cursor should go to the range that I have named 'Imperial', when the user clicks on the 'OK' button of the above prompt.
I'm trying to build an investment calculator. Tried searching "calculator" as keyword but return nothing similiar.
There are eight cell : [ A ][ B ][ C ][ D ][ E ][ F ][ G ][h]
[ H ] is price of the leverage
[A] is X balance
[b] is X lot size
[C] is X amount of money use to get x lot
[D] is X percentage of money use to get x lot
[E] is X point gain
[f] is X money gain
[G] is balance + money gain
Ignore the formulae for leverage to determine 1 lot price. Take 1 lot = 250.
What I'm looking for is when I enter any value at cell B, C or D, it will automatically calculate the value for either B, C or D cell.
Let say, the balance is 10000.
If I enter 2 at cell B. Then, it will calculate the value for cell C & D.
If I enter 750 at cell C, then it will calculate value for cell B & D.
If I enter 25 at cell D, it will calculate value for cell B & C.
how to use OR Selection in excel.
I am trying to display a message box for the user if there is "agency" in cell o8 but nothing in p8... I tried the following code but it doesnt work..
If Range("o8").Value = "Agency" And Range("p8").Value = "" Then
MsgBox "Please provide name of agency in cell p8"
Sheet9.Shapes("cross").Visible = True
Else
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedI have a workbook with two sheets. The idea behind the workbook is an Interview Guide to be used just before an Interview. For now my problem is this.
Sheet 2 "Competencies" is just data. It stores competencies with their associated definitions and questions.
Sheet 1 "Control Page" is the sheet where the questions will eventually go. The user (Interviewer) will input data on the first two pages which will include name of candidate, date of interview etc. but they will also select 5 Competencies from the already existing drop down menus on page two. From there as the selections are made I wish for a code to copy the corresponding definition on sheet 'Competencies" to cells lower down in sheet "Control page".
I don't see a place to upload a file as I have a sample of the sheet ready to go.
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
I would like to know if there is a way to expand the selection of cells from the current cell you are on to the very bottom cell available in the spreadsheet.
For example, I want to apply a formula from A12 - the very last A cell row in the spreadsheet.
I would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.
View 9 Replies View RelatedI have an embedded chart on my worksheet.I can select a cell behind the chart using the keyboard arrow keys.Is there a way of doing this using a mouse click,so that I know which cell i am pointing to/choosing?
View 4 Replies View RelatedI am working on a project where user has to select an item from a listbox, however there is a condition that if user select an item, other items of listbox should be disabled so that he/she can not select any other item.
Is there any way to do that as I tried
VB : Listbox.enabled = False
and
VB : Listbox.locket = True
in Listbox - Click/Change but no desirable results got.
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
It is actually a form - much like Access. I have protected it so it can't be changed. They can click on a particular cell, but as soon as they try to enter anything it gives them a message and entry is not allowed.
Is it possible to even prevent them from selecting an individual cell, i.e., restricting them to only clicking the control buttons ? I realize this is a spreadsheet, other products would probably have worked better, etc., however it's kind of like the last minor hurdle before releasing it for general use. The world certainly will not stop if I can't - just thought I would check with the expers.
I would like assistance as to select a range where the end can be any cell.
The top of the range is fixed, but the bottom gets altered when you insert rows. I have used the "Selection.End(xlUp).Select" command. It does give me the end of the range that I need. How do I store this location if I need to select that Range? I need to keep this value as I need to select that range and print it.
I would like to paste pictures from the clipboard to my workbook. Currently I'm using this piece of code to paste the picture, which works, however the cell need to be selected for this to work.
[Code] .....
The code I want to use would start like this:
[Code] .....
Is it possible to click on a cell and a monthly calendar pops up for you to select a date to input? The output will be just the DD/MM/YY format.
View 10 Replies View RelatedI have a worksheet that has only 2 input cells. Everything else is display only. I know I can protect cells from being changed, but can I format a range of cells so that the user cannot even select the cell?
View 2 Replies View RelatedI have is is some code that ends up selecting a certain range, as an example A2:A60 but this could be any range (A2:A65, B2:B65 etc) what I need is a bit of code that selects the cell 2 rows down from the last cell in the selection.
so for the example above, if A2:A60 is selected, the bit of code after would select the cell A62.
I created a very simple user form to input some data into a spreadsheet. I have a list box and this is my button code to input the data to a cell.
View 6 Replies View RelatedI am putting together an order expediting sheet.
I get the code to refer to single cells, but cannot get it to refer to multiple cells in the same column.
The code i am using is:
Private Sub CommandButton1_Click()
If Sheets("Sheet1").Range("E6") > Sheets("Sheet1").Range("B2") + 3 Then Sheets("Sheet1").Range("F6") = "YES"
If Sheets("Sheet1").Range("G6") = "YES" Then Sheets("Sheet1").Range("F6").Clear
If Sheets("Sheet1").Range("F6") = "YES" Then Range("F6").Interior.Color = vbRed
End Sub
I need to have the ranges E6, F6, G6 to refer to E6 - E10000, F6 - F10000, G6 - G10000
I have a a sheet with a drop down, if the selection of Critical in the drop down (A57) is selected, i need E57 to have a message saying we need to elaborate on the explanation or when critical is selected a popup with NEED TO ELABORATE in the description comes up.
View 1 Replies View RelatedI'm looking to go to every 8th cell in a column when I hit the enter key. I was thinking of selecting a small range say from A2 which is zero to A10 which would also be zero then A18 which is zero. The reason being I have a lot of figures to put in to each zero cell then I select "Series" to fill the cells in-between with the incremental figures between the two zeros which would be the difference between the two zero figures.
View 2 Replies View RelatedI've seen a preference setting somewhere that allow me to specify what happens with the cursor after entering a new value in a cell. such as moving down or moving right or not moving at all?
How do I code the cursor to NOT MOVE out of the cell after a new value has been entered?
I am looking for VB Code / Formula which will does the following:
Before the Cell is selection; it will shows a message for guidance purpose; but the moment the same is selected; the Validation list which I prepared will appear.
For Eg: before the Cell is selected; it will show as"< select from drop-down list>"; but the moment cell is selcted; the normal validation list will appear.
I know that there is Input Message option in Validation List; but this appears only when the cell is selected. I am looking for message which will appear before the cell is selected.