Activating VBA Code When Entering A Cell?

May 9, 2014

I am trying to write a small bit of VBA code, so that I can record the row and column of the active cell. I have got it working when I CHANGE the value of the cell, but I need to get the code to fire off when I ENTER the cell.

The code section is below:-

Private Sub Worksheet_Change(ByVal target As Range)
'Store the row and column into G1 and G2 for the drop down loading
Call RowNum(target)
End Sub

[Code]....

how I can do this, as I cannot find reference to a Worksheet_Enter function ?

View 2 Replies


ADVERTISEMENT

VBA Code To Lock Cell After Entering Data

Feb 2, 2014

I have a worksheet, i want to lock the data entered in a cell of a particular column . e.g. if i will enter the data in A2, A2 should be locked, when user enter data in cell A3, A3 should be locked. I want this code to apply on multiple columns (A,C,E,G).

View 3 Replies View Related

VBA Activating Code On Mouse Hover

Apr 19, 2013

Is there any way I can activate a sub routine when the user passes the mouse cursor over an activeX object.

For example this activates code when the user interacts with an activeX scroll bar:

VB:
Private Sub ScrollBar2_gotfocus()
Dim A, B, C
If OLEObjects("checkbox1").Object.Value = False Then
B = Range("B4").Value
A = (100 - B) / 2
C = (100 - B) / 2
End If

IS there a "gotfocus" equivalent such that code will run if the user simply passes their mouse cursor over the scroll bar EVEN IF they dont click it?

View 1 Replies View Related

Stop Screensaver Activating While Code Running

Jul 10, 2007

I have a file that shows a rolling screen and updates information every 30 mins. I want to be able to stop the screensaver from activating while this file is open.

I have found this code on the forum that does this, how do I call this function from another module? ...

View 4 Replies View Related

VBA Code For Entering Dates

Jun 13, 2013

I need a VBA code for entering dates. The start date cell is A15.When I select cell A15 and hit macro button it inserts todays date and fills in 1-month of dates in column A excluding the weekends...

View 3 Replies View Related

Lock Cells Automatically After Entering Via Macro Code

Apr 23, 2008

I have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.

View 2 Replies View Related

Formula Entering Zip Code And Returning Tax Rate From Established List

Oct 29, 2013

I am trying to create a formula that will allow me to enter a zip code and have excel return the specific tax rate for that zip code. I have zip codes in excel for all of California along with the corresponding tax rates. I am creating a form that I can just input the zip code and have the tax rate automatically pop in.

View 1 Replies View Related

First Code In VBA: Use A Inputbox For Entering The First Date For Monday Of The Month Each Time I Use The Worksheet

Jun 23, 2006

I am writing a module which have a different test based on for each of the previous 2 column cells. It calculate the days passed or in simple way calculate the date difference for the 2 columns and puts them in third. But i need to use a inputbox for entering the first date for monday of the month each time i use the worksheet.
When i try using the module for each cell of the column it display the input box for each cell. is there any way so that i just enter the value in input box and it can be used in rest of the module.Without using it again and again.

View 8 Replies View Related

Counting Number Of (-) Character In A Row And Activating Cell With Last Used (-)

Dec 3, 2012

I have a requirement where I have to count number of "-" character in particular row

I am using a excel database for promotion records of my team....The data base works as follows. I am using countif function to calculate number of associates in specific grade for specific month. One associate is getting promoted to next level, I will add one more row with the same employee name with change in designation and diff start and end date. I want to generate report for monthly promotions, The report should depict number of promotions in each month. ie in Jul there is one promotion from trainee to Engg for EMP1. There are hundreds of employees, eligible for promotion in each month.

A B C D E F G H I J K L
Emp Name Desig From Date To Date Apr-13 May-13 Jun-13 Jul-13 Aug-13 Spt 13 Oct-13 Nov-13
EMP 1 trainee 4-1-13 6-30-13 trainee trainee trainee - - - - -
EMP 1 Engg 7-1-13 11-30-13 - - - Engg Engg Engg Engg Engg

View 2 Replies View Related

Small Problem Activating Specific Cell Via VBA

Jan 2, 2007

Upon workbook open I have specified that I need a specific sheet & cell selected - so that the user can start typing as soon as the sheet is activated.

The cell is highlighted but I can not type into it unless I manually re-select it with the mouse or keyboard. Below is what i have attempted as well as a few other experiments. I even tried using the same code on sheets("ENTRY") using Private Sub Worksheet_Activate() - which works great as long as the workbook is already open but again not when freshly opening workbook -

Private Sub Workbook_Open()
'====================
Sheets("ENTRY").Select
If Range("E2") "" Then
Range("F11").Select
Else
Range("E2").Select
'Range("E2").Activate
'ActiveCell.FormulaR1C1 = "test"
End If
End Sub

View 9 Replies View Related

Excel 2010 :: Selecting Or Activating Cell Within Loop?

Apr 28, 2014

After adding worksheets, data to ranges, etc. I want to step through each worksheet and "Select" or "Activate" a specific cell (Mainly to clear all the range selections).

My code does the job except the second to last part of cell selection/activation.

Win7, Office 2010

View 3 Replies View Related

Activating Data Validation Based On Cell Contents

Dec 19, 2012

I am trying to see if it is even possible to have data validation applied to a specified range of cells, but if the value of cell 'B3' is '1' then the data validation will not run on the other cells (H4:G7)

View 2 Replies View Related

Re-entering Cell Value

Dec 7, 2009

In some of my formulas, I need to re-enter the contents of the cell in order for a formula to compute.

For example, I am doing some math on a cell whose original contents are "10:21:00 AM" The formula returns a #NUM!. If I retype 10:21 in the cell and hit enter, the formula will now calculate properly. I have reformatted the entire column several times so that it is a custom hh:mm format. The original content also shows as time so I don't think that it is a formatting issue.

My worksheet has about 20,000 rows and this only happens for about 100 of the rows. It is pretty time consuming to manually go fix each one. Any ideas what is going on here?

View 6 Replies View Related

Entering Data In A Cell

Jan 5, 2007

Earlier this morning I had a telephone call from a customer who uses a spreadsheet I created.

She explained that once she'd entered information into a cell [and clicked elsewhere] she couldn't then add to the former; rather, she had to click on the cell and type it all out again.

I assumed that, as an inexperienced excel user, she was unware of either double-clicking the cell, or selecting and pressing F2. I explained these methods to her.

She later emailed me with this:

"Further to our phone conv this morning I have just tried double-clicking on cells but it deletes everything in the cell rather than lets you edit what is there F2 does the same thing. This applies to any cell, whether it is text, date or number."

I've been using excel for a while now, and I've never come across such a thing before.

Has anyone else? Could it be to do with her own excel settings? (other customers have had no such problem)

View 9 Replies View Related

Create A New Tab When Entering Value Into A Cell

Mar 16, 2007

What I'm trying to do is enter a value/text into a cell - say (A1). After I enter the value/text I would like to have a new tab populated with all the information on the current sheet and the tab name referencing cell A1. I tried running a few different scenarios using macros and have come up short. I want to be able to do this multiple times with different values/text. For example if I enter B606123 into cell A1 I want a tab to be created named B606123. Then later I might want to add B606096 and have that created without effecting B606123.

View 3 Replies View Related

Commit A Format Change To A Cell Without Entering The Cell

Apr 8, 2009

I want to convert a range of cells to "number" format from text, and then refer to them in a formula that's somewhere else. However, if I do the format from VBA, I can't get the change to be reflected without the following

View 4 Replies View Related

Macro For Extracting Text From One Cell And Entering It Into Cell Beside

May 29, 2009

In one column (Column A) I have a column of data which contains long descriptive text - i want to extract a specific piece of text from this descriptive text and enter it into the next Column (Column B).

E.g. Cell A1 = "Hello Buddy, how are you?" - i want to extract the word Buddy and enter it into cell B1

then A2 into B2, A3 into B3, etc, etc,...

Is there a simple macro out there that can do this?

View 7 Replies View Related

Entering List Depending On A Cell Value?

Feb 6, 2014

i have 5 lists of names which i want the sheet to enter depending on the cell value of C1. C1 is a drop down of 5 different departments so when C1=department 1 for instance i want A4:A25 to automatically update the list of names that i have against that department.

im sure its fairly simply but i just cant get any formula i use to work!

View 6 Replies View Related

Entering Current Folder Into A Cell

Oct 29, 2013

I have been looking for a way to enter the folder where the excel file is located into a cell.

I have seen you can add the whole path (=CELL("filename") ), however I am only wanting the 'last' folder it is in. For Example:

C:docsfolderdataClass D4John WayneProfile.xlsx

Would enter "John Wayne" into cell. The cell is namely the "name" cell.

I work in a school and a teacher is wanting a couple of different excel files for each student (600 students) I am hoping that I can put the files in a folder with the students name as the folder. That way at least its only 600 names and not 600x7! The other details are similar on a lot of the files such as class (this is also one of the folders in the path).

View 3 Replies View Related

Entering Text In A Locked Cell

May 29, 2014

I have worksheet that contain values in column E and G and the total in column K. E and G are unlocked for manual input and column K contains the formula for the total and is locked. From time to time a certain row will not be applicable and before I started locking the cells in column K i would just put "not applicable". Now that it's lock I can't do that. My only option now is to put O in column E and G but I would rather put "not applicable" in column K. Is there a way around this. I was thinking of something along the line of a macro that would unlock the worksheet input the text and re lock it. The macro would have to lunch when a locked cell is selected or have a button that would launch the macro an would prompt for which cell the text would be entered. Is this possible.

View 7 Replies View Related

Lock Data In Cell After Entering Value?

Feb 2, 2014

0: i have a sheet 1 protected and few edit range (Col A,B,C,D,E and F).

1: I have a validation on multiple adjacent columns (like Col A, Col C, Col E).
2: I need a code where if i select value in col a (cell A2) cell B2 should get current date and Time and both the cells (A2 and B2) should be locked. when i select data in cell C2 , D2 should get current date and time(Cell C2 and D2 should be protected).
3: User can enter data in A3 and B3 will get current date and Time (now A3 and B3) should be locked.
4: I need a function where user can call it and unlock the locked cells and edit the data and re-lock the cells again.

apart from the Col A,B,C,D,E and F, other columns and cells remain locked.

View 2 Replies View Related

Entering Multiple Cell Formulas?

Mar 4, 2014

I have a requirement to enter 25 formulas into 25 different cells. The formula is this:

[Code]......

The only thing that changes is the cell reference at the end which goes up in increments of 20,000, i.e. R20000, R40000, R60000, etc.

I have this set up and it works fine. My question is whether there is another quicker way rather than have the above code repeated 25 times?

View 5 Replies View Related

Cell Protection After Entering Data

Mar 1, 2012

Having the cell protected after entering the data. From the below table, i need when a date is entered and moved to next cell, the cell which contains the data should be protected. I need to this for the comments column as well.

Number
DateStart timeEnd TimeActual Time takenComments
123459132546132156135469654814846543

View 1 Replies View Related

Call Macro On Entering Cell Value

Feb 9, 2013

I have a macro that is woking that is activated either via Ctrl-p or by an icon in my quick links.

I would like to call the macro when I enter text in the input cell C7.

At the moment I enter text in C7 and then call the macro to perform a series of tasks.

I would like to make the macro and tasks perform when I enter the text in C7 and hit return or enter key.

Is this possible??????

View 3 Replies View Related

Initiating A Response When Entering Into A Cell

Mar 20, 2008

I have an Excel worksheet that when a user enters something into cell A1 I want to clear cells (b2:b20). If I use an object, I can attach a macro to it, but I don't want to use an object. I want to use an Excel cell and then when the user types into it, I want to perform an action.

View 9 Replies View Related

Divide By X Automatically In Same Cell Upon Entering A Value

Mar 24, 2007

How do I enter a value into a cell and divide that number automatically by another value all within the same cell.

For example..

I want to enter 20 into a cell and have it change into 17.24 (20/1.16) automatically all within that same cell.

View 3 Replies View Related

Prevent Someone From Entering A Formula In A Cell

May 18, 2007

I'm creating a maths workbook in Excel. I want the user to workout the answer in his/her head and enter the answer into Excel. The only problem is, of course, that there is nothing stopping a user from simply typing a formula to obtain the answer! I have tried all data validation, apart from Custom which I can't figure out. Is there a way to prevent a user entering a formula on a worksheet?

View 5 Replies View Related

Hyperlinks From Userform Entering In The Wrong Cell?

Jun 24, 2013

I'm putting together a userform and all the values and ending up in the correct place except for the two hyperlink cells.

I think it is because I've got the anchor wrong but Im not sure how to correct. I'll put the whole form in so if there is something fundametal going wrong in the early stages I can be put right, but the hyperlink issues are down the bottom at the Offset 16 and 17.

VB:
' Write data to worksheet
RowCount = Worksheets("Resources").Range("A2").CurrentRegion.Rows.Count
With Worksheets("Resources").Range("A2")

[Code]....

View 4 Replies View Related

Cell Auto-lock After Entering Time

Sep 16, 2013

I have created a time sheet and I would like to know if there is a way to auto-lock cells after time is entered so that it cannot be changed. As of right now I have employees enter the time by using Shift+Ctrl+;, so the exact time is recorded, however employees can write in whatever time they want. so I guess what I want to do is 2 things:

1- can the cell be formatted to only accept Shift+Ctrl+;, so that the exact time is entered in the cell and prevent employees from entering times they were not at work?

and

2- can the cell auto-lock after Shift+Ctrl+; is entered so that they can't modify the time sheet the next day if they arrived earlier than the day before?

View 5 Replies View Related

Entering Data In 1st Blank Cell In Specific Row

Mar 5, 2014

I have created a form where a date and remark is added every few days. I am using Phone Number as the base of VLookup. I want a code so that the date and remark which is entered in a Text Box, is stored in the first available blank cell pertaining to that employee. I want the first date and remark to be under "Date 1" and "Remark 1".

Thereafter, if another remark is subsequently added for that employee, the form would ask to enter his phone number to identify which employee the remark pertains to. Then the Text Box where remark is entered should be stored under "Remark 1" if there has been no remark before, "Remark 2" if "Remark 1" already exists, "Remark 3" if "Remark 2" already exists and so on.

I do not want to enter it in a new row. I want to enter the remark in the next blank cell of the row which matches the employee Phone Number.

Untitled.png

View 5 Replies View Related







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