With Excel 2000 Hyperlinks to other locations in a Workbook such as an Index to other worksheets can be achieved (executed) with the Enter Key. When using the same file in Excel 2003 the only way to execute the Hyperlink is by clicking with the Mouse.
Is there any way to get Hyperlinks to operate the same (with the Enter Key) in Excel 2003?
In the following code, I have a find sub... when the user enters a date and hits the "Enter" key, is there a way to bypass the "Ok" key?
Private Sub cmdFind_Click()
Dim ws As Worksheet, myDate
Dim rFoundDate As Range
'check for valid distribution date (between October 1, 2006 thru December 31, 2014)
myDate = txtFindMyDate
With myDate
If DateValue(txtFindMyDate) < DateValue("10/1/2006") Or DateValue(txtFindMyDate) > DateValue("12/11/2014") Then
MsgBox "Please enter a date between October 2006 and December 2014"
.SetFocus
Exit Sub
End If
End With
Cells.Find(What:=txtFindMyDate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Unload Me
End Sub
I have produced a basic search/lookup facility on an Excel workbook that simply allows the user to enter a post code and some details will be returned using a vlookup. The document is going to be rolled out to a number of operational users so I want to basically 'lock down' everything I can in the document (basically everything except the data entry cell) and make it fool proof- I have locked all cells apart from the data entry cell and have made the file read only.
The only issue I am encountering is when the cursor is in the one 'unlocked' cell (i.e. the one the users will enter the post code into); it appears that you can break the document. For example, when the cursor is in the 'unlocked' cell, I am able to go to Tools > Options and change various settings including cosmetic colour changes but also cell calculation which breaks the lookup functionality. This is probably enabled as the cell is unlocked, but if I lock the cell, when I protect the document, it disables data entry!
if there is another way of providing this one cell for users to input data into for the vlookup to work whilst locking down the rest of the document to ensure that no-one can break it?
I'm reading a hyperlink into a spreadsheet using an =index,match,match formula. The Hyperlink appears as the correct one when you read it in the cell, but it does not link to that address....
View 8 Replies View RelatedIs there code for worksheet activate event that will reprogram the Enter Key to mimic the Tab Key such that if you hit Enter, the cell to the right of the active cell is selected? I've searched the net for several hours today and find nothing that addresses this question.
Also, the Enter Key would have to be normalized on worksheet deactivate.
How would you activate a button, if one hits enter while in the cell that contains the button?
View 9 Replies View RelatedI am trying to write a macro that activates the following funtion in cell D1.
=HYPERLINK("#"&C2,"")
I have recorded the following macro but it only jumps to the cell the function is in rather than actually activating the function and jumping to reference which has been selected in C2.
Row 10 contains a dropdown list and a value that needs to be givin in. I would like to create a hyperlink that activates a macro.
That macro should: Copy the row with the dropdown list and paste it under the last one.
So for example if you click the hyperlink once, it should add it to row 11, if you click it again, it should add it to row 12.
Is it posible that when data or text is entered into a cell and the enter key is pushed that this can either run a macro or activate a hyperlink to go to another worksheet for user to follow further instructions?
OR instead of going to another worksheet a pop up message appears with instructions and then can be OK'd to remove message and proceed?
I have developed a Userform button in Excel 2010 using the developer icons, which when clicked on with my mouse it runs my super dooper macro. It works great!
But one thing I want to have happen is to have the choice of using the Enter key to start the macro or use the left mouse button
For example, I place data in (say) cell A1 and the userform button is in cell A2. When I place the data in A1 and press the Enter key, the cursor moves down to A2, but doesn't highlight the user button. When I press the Enter key again, the cursor moves to cell A3.
What I want is when I fill in the data in A1 and press the Enter key, the cursor moves to cell A2 and selects the button, so that when I press the Enter key again, it activates the macro.
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
[Code].....
This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.
View 3 Replies View RelatedI have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
I have hyperlinks betwene one worksheet to another - they only only hyperlinked thourh column & rows (eg: A100) etc. How can I lock these hyperlinks but still allow users to insert new rows without losing their place?
View 2 Replies View RelatedI am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
In my file i can insert text to a certain cell and column but this is for one entry only. I am wishing for a run that for every entry, there is a new row.
Here's my file sample project.xlsm
You can see there the user form for a post entry. The user should fill-in code and description and if he hit save, the code and the description should be recorded on list provided the sheet.
I'm not that clued up on VBA or macros but I do have a few i regularly use, I’ve found a thread related to my problem but cant make much sense of it.
Basically i want to post the 3 cell values (C5,C7,C9) into row B20,C20 and D20 respectively. However I want these values to form a list staring with row 20 and working down onto the next available/empty row.
Marking My Post As? How do i mark my post as solved?
View 5 Replies View RelatedNot sure how too do this, here is a simple example of what I want too do. Just using different buttons to post a number.
View 6 Replies View RelatedI have a list of UK Post Codes that I wish to filter but because of the format I'm getting problems.
The reason is that I'm only interested in the first part of the post code.
Example: Using IP7 6NH and IP32 8LR. If I tried to filter those using greater than then Excel would treat IP7 6NH as larger then IP32 8LR because 7 is larger then 3.
The post codes are all imported from another piece of software and are always in the format of 3 or 4 chracters, then a space and then 3 more chracters. I wish to create another column which only has the first part in it. So I wish to crop IP7 6NH down to just IP7 and IP32 8LR down to just IP32.
Then I want to make a further column which looks at the value in the first new column and returns "In" or "Out" based on some conditions.
These are the conditions. If the cell begins with CO or NR or CB or CM or PE, regardless of the rest of the value, then "Out" should be returned. If the value is between IP1 and IP6 inclusive then "Yes" should be returned.
If the value equals IP7 then "No" should be returned
If the value is between IP8 and IP17 inclusive then "Yes should be returned.
If the value is between IP18 and IP29 then "No" should be returned.
If the value equals IP30 then "Yes" should be returned.
If the value is bewteen IP31 and IP33 inclusive then "No should be returned"
I'm think that it's quite complex and maybe the way to go would be to have two other columns, one containing the "In" values and the other containing the "Out" values, that a formula can refer to when making it's yes or no choice but I don't know how to do that.
I may have missed it, but I can not seem to find where I can customize Excel to add a "paste formula" button. (I have a fair amount of copying to do where I do not want the formats being changed at the same time.)
View 9 Replies View RelatedI have the following code. It takes data from one sheet and inputs into another. When I enter a Customer ID in cell G12, starting on Row 18 and proceeding down, jobs are listed. When the macro posts into the history sheet, it posts the jobs in rows. I wish for each time I run the macro, the info is all in the same row whether theres two jobs or 20 for that customer ID. Right now it is posting like this (two jobs listed):
CustID(G12) | Name(B12) | Address(B13) | Job1(A18) | Job1Date(D18) | Job1Price(G18) |
CustID(G12) | Name(B12) | Address(B13) | Job2(A19) | Job2Date(D19) | Job2Price(G19) |
Im trying to make it look like this:
CustID(G12) | Name(B12) | Address(B13) | Job1(A18) | Job1Date(D18) | Job1Price(G18) | Job2(A19) | Job2Date(D19) | Job2Price(G19) |
Sub InvoiceUpdateHistory()..................
Is there a way with a formula to have a result of one cell pasted in a different cell? ie. C1's formula =if(and(isnumber(a1),isnumber(b1)),b1-a1,if(and(isnumber(a1),isblank(b1)), b1 = d1,"")
A B C D
12/18/06 formula above =today()
if b1 isbalnk it will copy the value of d1 to b1. I dont know if this can be done i have looked at offset and a few others but it doesnt look like this can be done but want to see if i have over looked domething
If the user clicks on a cell within a specified range ("DataEntryWindow") which contains formulas, I want the formula RESULT to be copied on to the formula bar for that cell (not in to a cell) - the user can then press enter to replace the formula with the result, or escape to cancel
- you can do this manually by copying the cell as values to another cell, then copying that value to the clipboard letter by letter, then clicking on the original, clicking F2, selecting all the formula text, then pasting the clipboard!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("DataEntryWindow")) Is Nothing Then
curval = Target.Value
Application.SendKeys "{F2}"
'Application.SendKeys "???" ' some keyboard combination to go left, left, left
Application.SendKeys curval
End If
End Sub
Needless to say, it doesn't work. sendkeys seems to do nothing at all. is there an object in worksheet for the formula bar that i can paste to?
Where do i find the numbering add ins for Excel 2000? I receive a message
every time I open a document that says: I must load them for optimal
numbering and toolbar behavior. I have looked in the add ins by going to
add remove program, selecting Microsoft office, then change, then excel.
under the add ins there is nothing that says "numbering". I have also looke
on line for a down load at the Microsoft web site.
I am trying to read a value in a cell to post in another sheet.
Once the value has been copied, end. However, if the cell to the right of the copied has a value, then that value should be copied instead.
If the cell is blank, then the information should not be copied.
Basically I have a string of data from left to right being populated, with the most current value being copied to a differnt sheet.
I am creating a 'add supplier' userform, in which i open the userform, type in the new suppliers information, then press submit.
The problem is that the data i enter deletes the data that i previously entered into row 26 instead of posting the data into a new row (I wish it to be 27, then 28, etc)
Here is my current code for the userform
VB:
Private Sub CommandButton1_Click()
nextrow = 26
Sheets("Suppliers").Cells(nextrow, 3) = UserForm5.TextBox1.Value
Sheets("Suppliers").Cells(nextrow, 5) = UserForm5.TextBox2.Value
Sheets("Suppliers").Cells(nextrow, 6) = UserForm5.TextBox3.Value
Sheets("Suppliers").Cells(nextrow, 7) = UserForm5.TextBox4.Value
[Code]....
From the attached data is it possible to create a formula to count the number of staff in post that are line managers?
View 7 Replies View Relatedis there a function that will return the specific content of the cell. like for example, i have a cell that contains the word DOG(a1), and on the other one is a sentence like THEDOGBARK(b1)...the function must find the word dog and must put it in another cell(c1).
find (a1) to (b1) and display the word in (c1)...
ex1.
a1 = dog
b1 = thedogbark
c1=dog
ex2.
a1=bark
b1 = thedogbark
c1=bark
ex3.
a1=the
b1 = thedogbark
c1=the
I am in a process of creating a excel based productivity for my team. This workbook has 2 worksheet, one is QA productivity tracker which is accessible to all, after the information is filled i want to store it daywise in a master tracker (next worksheet).
I have created a macro which copies the information to master sheet, but the challenge i am facing is that at every execution, macro should paste the data in next row.