Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Application.onkey With 'enter'key


Have working routine with combobox lists which are split into 'beverages of sorts' - sent to textbox and from there put into columns of type/variety in other worksheets
All working fine apart from repeated 'hits' of the command buttons to activate the data movement

Have Excel2003 on XP Pro and have been trying to use the application.onkey sub to try and introduce the 'enter' key to try and make things easier
but......not winning (aaaagh)
code being used just now is

Sub commandbutton1_click()
Sheets("invsales").Activate
Worksheets("invsales").Range("value").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TextBox6.Value
End sub

As you see from 'textbox6' - I have a few of these for different beverage types and costs
Would be grateful for any help in introducing an 'ENTER' keystroke into the code somewhere.....to make it that bit more 'easier'


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Application Onkey: Prevent Users From Using The Print Screen Or The Alt Print Screen Buttons On The Keyboard
I'm just looking to prevent users from using the print screen or the alt print screen buttons on the keyboard. I have this script that works if I use "39" (Right Arrow)but wont work if I use "42" (Print Screen Button).

View Replies!   View Related
Onkey Or User Interrupt
This is still the continuing saga of the Toastmasters countdown speech timer. I got past the timer loop issue but now I need a way to detect (preferrably) the spacebar being pressed during the timer loop. I want that keypress (or any other key if spacebar isn't do-able) to end the timer loop and run the WriteLog routine.

I am coming to the conclusion that Onkey is not what I want.

View Replies!   View Related
Onkey Method With Parameters
I need to assign a procedure to the OnKey method, but VBA does not let me specify arguments with it.

View Replies!   View Related
Use Onkey To Make Something When A Key Is Pressed
Onkey is used to make something when a key is pressed. There is a instruction to make something when any key is pressed?

View Replies!   View Related
Resume Keys Normal Function After OnKey
I use the following code to assign a macro to right arrow key.

Application.OnKey "{RIGHT}", "myMACRO"

My questin is how to let the right arrow key resume its normal function, that is move from left to right (A2 to A1) if I do not want the macro any more and without quit and restart EXCEL? The following code disabes the right arrow key's normal function. Application.OnKey "{RIGHT}", ""

View Replies!   View Related
Application.DisplayAlerts Freezes Application
Im copying and pasting data from one workbook to another but when I want to close the source workbook, it comes up with this message that I have much data and if I want to keep this in a clipboard. I thought I could disable this with Application.DisplayAlerts = False but when I do this, Excel freezes. Im I doing something wrong. How can I supress this window?

Public path As String
Sub Get_data()
path = "\Nlchoosa.nlOPS_Processes$OPS_ProcessesReports Sector performance"
Workbooks.Open Filename:=path & "ReportsSector Performance Reporting week.xls"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Windows("Sector Performance report Week.xls").Activate
ActiveSheet.Paste
Application.DisplayClipboardWindow = False
Windows("Sector Performance Reporting week.xls").Activate
Application.DisplayAlerts = False
End Sub

View Replies!   View Related
Formula Must Be Confirmed With CTRL+SHIFT+ENTER Not Just ENTER
This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.

View Replies!   View Related
Application Run
I want to make a macro where it will use the current filename of the workbook I have open (where is says New Quote Sheet 2.xls below) Is there something I can put where it will use the current filename when the filename is changed?

New Quote Sheet is a read-only template, and when a new quote is started, it is renamed.

Eg.
Application.Run "'New Quote Sheet2.xls'!Part8"
Sheets("8-Part").Select
Sheets("8-Part").Copy After:=Sheets(25)
Sheets("8-Pack").Select
Sheets("8-Pack").Copy After:=Sheets(26)

View Replies!   View Related
Application.IgnoreRemoteRequests
With the help of Professional Excel Developement by Bullen Bovey and Green, I am building a Dictator Application. All-in-all, it is coming very well, EXCEPT that I can't seem to get the IgnoreRemoteRequests setting to get written to the registry properly on ShutDown.

On Open, I change this setting to True. In the BeforeClose event, I reset it to False, along with all of the Settings that I hosed on Open. All of the other settings get properly saved on exit, however, the IgnoreRemoteRequests is still set to True the next time Excel gets re-opened.


View Replies!   View Related
Application.VLookup In VBA
I've probably spent the last 4 hours reading posts from a bunch of different boards as well as this one, and I can't seem to pinpoint my problem. I'm trying to access data from a closed workbook via the following:

'Dim x As Variant

'x = Application.VLookup(Cells(1, 1).Value, Workbooks("C:Documents and SettingsmeMy DocumentsmyOptionsProgramsLiveUpdate.xls").Sheets("LIVE UPDATE").Range("$A$1:$C$5"), 3, False)

Cells(1, 2).Value = x

It doesn't seem to be working as I get the following error:

Run-time error -9

Subscript out of range

View Replies!   View Related
Usage In Application
done some VBScript spreadsheet reporting involving basically inserting data and some formatting.

I need some advice on what approach to take with Excel on my current project. The basic goal is to scrape project requirements off a word doc and load them into Quality Center (a test management tool from HP, henceforth referred to as QC). I have an hta/vbs process that offers the user choices gleaned from the QC API, scrapes the word doc, and creates a spreadsheet with a row for each requirement and a column for each property a "requirement" has in QC. These values are a combination of data from the word doc and values selected by the user in the preceeding hta interface.

The reason for this Excel step in the process is that all the values chosen up to this point are generic for the entire project. But there are a few properties that will be requirement specific. So all the info is collected in Excel and the user is to then go through each row and choose the "target cycle" (which I pulled off QC earlier in the process and display in Excel as a dropdown list). The "Platform Folder" and the "Module Folder".

Ok, so with that backround, here is my quandry... In the hta interface the user selected all the "Platform Folders" the project will have requirements in. These are then presented in a dropdown in the Excel, so the user can choose which of these Platform Folders this specific requirement should be loaded into. What I need to do is then have the "Module Folders" to be a dropdown that is dependant on the "Platform folder" chosen.

This is difficult enough for me, creating a dynamic dependant dropdown that will be replicated on each row. I don't know exactly how to do this but feel I can probably figure it out with all the help offered on these MVP sites.

What I can't figure out is where to do this. Should I set up the lists, formulas, and control code dynamically? Can these dependant list derive their values from comma delimited lists (like I did the non-dependent lists) or must they be in a worksheet? The only way I see to make the dependent dropdown work correctly is to have it run off a worksheet event. Can worksheet event code be applied dynamically or do I need to have a "template" type workbook containing the code in the application's folder and use that instead of creating a new workbook at exe time? I still have to write the upload into QC code, and that will probably be a macro that the user will have to install into their personal.xls. Can the dependent dropdown code be installed in the same way even though it needs to be event driven? Another aspect is getting the data for the dropdown. I can either get the lists earlier in the process when I already have a connection to QC and pass it along, or I can connect to QC from Excel and get the values dynamically. I am assuming passing them in will be more efficient.

View Replies!   View Related
Open New Application
I need a code to open a new Excel Application (Not just a workbook).

eg: say I already have book1.xls opened. Now I want a new excel application (say book2.xls) to be opened. Now, when I close any of the books (by using the cross on top right corner), the other shouldn't be closed.

View Replies!   View Related
Database Application
I am very new with Excel and VBA, what i need to do is:
develop an initial log in to open an excel workbook...
and then open the workbook if username and password

1. ask for user name (field should be 50 characters)

2. Ask for a password (password alphanumeric with special characters field 25 characters)

3.Validate user to a salesman data base
if log in attempt fails after 3 attempts, purge buffer, clear log in screen and lock keyboard for 60 seconds)

I have done the whole excel application all i need is this thing to pop up when double click in my direct access to it..

Also save document with the invoice number that in in a cell

and How can I make a cell auto increase the invoice number every time open workbook?

View Replies!   View Related
VBA To Open Another Application
understand the procedure for opening another application from Excel using VBA?

First check if open, if so, activate, if not, open and set to active?

Any help would be great! -even links to other posts or otherwise

View Replies!   View Related
Vlookup From Another Application
The problem is I need to do a vlookup from another Office application. I need VBAcode that:

opens up an excel spreadsheet,
performs the vlookup
and then retrieves the associated value.

View Replies!   View Related
Using Application Events With An Add-In
I've created an addin to reformat spreadsheets that I receive in a particular format. What I would like to do is enable events so that whenever a spreadsheet is opened the reformatting procedure is run (this also validates whether the spreadsheet is of the correct format).

I have created a class module with the following code (exactly as the Excel help):

Public WithEvents App As Application

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
SortE1Output 'This is my procedure that determines whether the
'spreadsheet is of the correct format and then reformats it
End Sub

"After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur."

When I try to make a call to the InitializeApp procedure in the auto_open procedure (in a different non-class module) I get a "compile error: sub or function not defined".

View Replies!   View Related
Start A New Application
I have an excel file which automatically loads an user form once the file is opened and when the user form is closed, the file also closes. When the file is open i am not able to switch to anyother excel file because this userform is active. Is there a way, when i open the file with userform, a New excel application starts. So that i can use the other excel files opened by the previous excel application.

View Replies!   View Related
Mouse Over In Other Non VBA Application
Is it possible to get mouseover information from outside of Excel using VBA? I'm trying to automate a non-microsoft package but I need my mouseclicks to wait for a message to appear within the other application before my macro continues. I've already asked something similar before but I've put a better title on this thread so apologies for the repeat.

View Replies!   View Related
Application.filesearch
I have created a macro that must extract data from other workbook. It opens the workbook, with an exist, and isopened control, and then open it. Its running well, but i found while making another macro, that if the file direction is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", then i cant run the exist control. Next you have the code im using. In the first version, i had .LookIn = ThisWorkbook.Path. Now Ive tried:

1. Dont put .LookIn, and fName is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls"
2. .LookIn = "http://www.xxx.com:8080/"
.FileName = "Challange%20Cost%20Proposal%20Sheet.xls"

Both are failing, it says, the file doesnt exist. If i try directly WorkBooks.Open "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", it opens correctly, but i would have a control, because the original file name could change, and the macro would crash if does.

Function OpenWorkBook(fName) As Integer
If Exists(fName) Then
If (IsOpen(fName) = False) Then
Workbooks.Open (fName)
OpenWorkBook = True
Else
MsgBox "The WorkBook " & originWorkBookName & " is already open."
OpenWorkBook = -1
End If
Else.........................

View Replies!   View Related
Minimize Application
i want to minimize the excel application and show a userform on startup.

View Replies!   View Related
Hide A Application
If you run a macro like this: Application.Visible = False. How do you get back to the application

View Replies!   View Related
Application.Run Macro In Different Workbook
Ive got a script in which I call another script using the Application.Run method. The exact line is below:

Application.Run "'" & wb & ".xls'!save_case", sheetname, fieldvalue
The macro runs fine, but after that a different workbook becomes active. There are a couple more lines of code after the line above that don't get executed (ive tested this by putting a MsgBox command after it).

How do i pass control back to the starting subroutine? Or should it and there is something wrong with my code?

View Replies!   View Related
Application.FileSearch Error '445'
The following code works fine in Excel 2003 but returns the following error in Excel 2007:

Run time error '445'
Object does not support this action

Sub List_Files()

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = "C:DepartmentsInventory"............


View Replies!   View Related
Application.ScreenUpdating = False
I am using the following code to turn off the screen updating during code execution
Application.ScreenUpdating = False
Is there anything that would still show the screen updating desptite being set to 'False'?

View Replies!   View Related
VBA Application.WorksheetFunction.Match
I have a large amount of data (10 columns X 1200+ rows). I want to copy some of the columns using the sorted names in the first column as a Match in the macro when the Matched cell (for comparison) is on a different sheet. I want to copy five (5) columns of data from the list on Sheet2 to Sheet1. The columns are B through F. I do not want to copy column A, but want to use the contents of the A column cells to determine which rows to copy.

I lock down the workbook and send it to other people and I want them to just pick a name from a drop-down list that will generate the Match cell on Sheet3 which will then populate Sheet1 with "Matched" data from Sheet2.

(I think I just confused myself.)

Part of my code is:

[code]

Sheets("Sheet2").Range("B1:F" & Application.WorksheetFunction.Match("XXXXX"),Sheets("Sheet2").Range("A:A"))).Copy Sheets("Sheet1").Range("A1")

[end code]

I want the "XXXXX" to refer to a cell on Sheet3. But I have been unable to solve the problem after trying a variety of fixes; including naming the single cell on Sheet3.


View Replies!   View Related
Application.Filesearch 2007
Filesearch......

Just been upgraded to 2007 and computer says no......

Iv only been VBA'ing for around 6 months and really am still very new to it.....

The bit that goes wrong is:

View Replies!   View Related
Application InputBox Cancel
Application.InputBox ("test")
I get a cancel button, I want this to show a message and then exit the program.

My entire porgram is in one Sub.

View Replies!   View Related
Difference Between MAX And Application.WorksheetFunction.Max
what is the difference between them.
If i know how many rows there are in a column I guess i can just use MAX, right?

View Replies!   View Related
Application Max Function Options
I use the following (example only) to get the maximum value in a defined rang:

Application.max(sheets("Sheet1").Range(a1:z220"))

Is there a way to inlude the requirement to look only in rows and columns that are shown (thta is: NOT hidden)


View Replies!   View Related
Application.Input Box Validation
I'm trying to get input from a user regarding parameters for loan calculations. I'm still very new at VBA so some of this will seem very silly, but I've been trying for hours to get this to work right and I keep fixing problems and creating more simultaneously. I've come to the conclusion that I have a fundamental misunderstanding of how this works.

This one actually works fine, but I am including it just in case I've done something less than perfect and someone can correct it.

View Replies!   View Related
Close Application Via VBA From Access
Looking for code that will verify if Excel is running and if so, then close Excel, including all hidden versions.


View Replies!   View Related
Application.OnTime Not Stopping
I am running a timer in XL using the Application .OnTime Method.

Everywhere I've looked tells me that the procedure to stop the timer is

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=False
End Sub

I have this code in with the timer, but when I hit F5 or go to Tools--->Macros--> and Run the StopTimer Macro the timer just keeps running.

how I can properly call this procedure and stop the timer?

View Replies!   View Related
Crashes With Application.quit
I'm trying to close out excel through a user form (excel is hidden) button but excel crashes when it reaches the application.quit line of code.

I searched google and found one message that said that if you have add-ins that they can cause excel to crash when closing but there was no explanation or work around.

Is there a way around this problem?

View Replies!   View Related
Code That Used Application.FileSearch
It seems to me that Application.FileSearch is not working with XP SP3.

I have a code that used Application.FileSearch and it works perfectly at home. But when I use in the office, it does not work. When I click on HELP menu, the one in the office has a Microsoft Excel 2003 SP3 whereas the one at my home does not have the SP3.

View Replies!   View Related
Macro That Run The Application GetOpenFilename
I want a macro that run the application GetOpenFilename (or something that is similar) but when I push the OK button, i don't want to open the file, I just want the filename of the file I have browsed to.

View Replies!   View Related
Unhide A Hidden Application
Already read some threads about how to hide and unhide an excel application.
I was able to hide my excel application, but, somehow I'm not ale to unhide it when I close my Form:

View Replies!   View Related
Hide Application Main Bar
how to hide the main bar from Excel? the one where says "Microsoft Excel - "Name of the workbook"


View Replies!   View Related
Macro Range Application
I have a worksheet using several columns and 100 rows. Column B and column C can contain a value "x" in either coulmn but not both in a specific row. The macro illustrated works perfectly for what I need but there must be a way to point the macro to a range of rows rather than using an "if" statement for each row as I've done.

View Replies!   View Related
Intersect 2 Ranges In One Application
When trying to intersect ot ranges in an application it selects all ranges example:

View Replies!   View Related
Open An Spreadsheet Except Through The Application
I cannot open an Excel spreadsheet except through the Excel application. This includes through Windows Explorer, on the internet (this forum), in email. I need to save the file first, then open Excel, then open (through Excel) the file.

In Email, I get "System cannnot find the file specified" On this site, I get "Access to the specified device/path is denied" From Explorer, "Cannot find the file.... based on the criteria xls(or one of its components). Make sure the path and file name are correct and that all required libraries are available."

Last time, our IT group couldn't fix it and had to reinstall the entire Office package to get it working again. I'd rather not go through that.

View Replies!   View Related
Application Defined Error
I am using this code in an add-in and I get an Application defined error in the
ActiveSheet.Range(cell.Row, 14).Value = "A" line


Dim cell As Range
ThisWorkbook.Activate
For Each cell In Selection
ActiveSheet.Select
ActiveSheet.Range(cell.Row, 14).Value = "A"
ActiveSheet.Range(cell.Row, 15).Value = "B"
Next cell

View Replies!   View Related
Application.EnableEvents Best Practice
Am using a number of checks on Worksheet_Change and Worksheet_Calculate events and, depending on the result of the change, resetting a cell value.

For each of these I am using

Application.EnableEvents = False
Range("A1").Value = 0
Application.EnableEvents = True

to avoid that the change of A1's value triggers a calculation event and gets me into a loop.

However, something in my project is setting EnableEvents to False and not resetting it. I've searched the entire project for all instances and each "=False" is swiftly followed by a "=True". The code is not causing an error

The only way to get around this is to have a user press a button to re-enable events, but I can't tell them to press the button because there are no events to trigger the message!

View Replies!   View Related
Creating A New Application Through Macro
I want to create a new excel application through macro. I basically want to transfer few columns from the parent workbook to new workbook. but these new workbook must be contained in a new excel application.

View Replies!   View Related
Activate Web Page Application
Is it possible to have Excel place a mouse click on another program through a VBA code or another method? For example, my Excel sheet imports live data from another program and performs calculations on this data. If for example cell A3 is greater then 10, I would like to have the program click on another application window. Is this even possible? Who can I find to write this code for me? Excel NOOB here.

View Replies!   View Related
Application.Calculation = XlManual
I just want to set the calculation to manual upon opening a file and I am using the following code but it is not working. The code is in the "ThisWorkbook" module...

Private Sub Workbook_Open()
Application.Calculation = xlManual
End Sub

View Replies!   View Related
Open/Run Another Application/File Using VBA
I need to open/launch
-applications like Outlook, IE
-files like .bat, .xls,
-folders (with windows explorer)

using VBA.

How to code this?

View Replies!   View Related
Application.DisplayAlerts Not Working
I am trying to suppress the message box displayed while executing the macro.

View Replies!   View Related
Save Workbook As Application
I've been informed that an excel workbook can be saved as a vba application -

View Replies!   View Related
Close And Save Application
When a user X's (clicks of the X in the titlebar) of a user form, I want to save whatever input has been made to that point and close the application. this is what I tried but it neither saved input nor closed the application

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved