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


Advertisements:










Event Keypress, Tab Or Keydown And Setfocus


How to setfocus on a cell, when user press enter, key up, or tab ?
examp. :

i want setfocus to cell A5, when i press enter or key up on cell A2.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Keydown To Effect To All Controls That Support Event
Is there a way to fix a private sub to catch keydown effect of various combos and textboxes of my userform?

Instead of typing the same thing to each and every control's keydown event just call a sub. Code goes like:

Select Case KeyCode
Case 112
ShellExecute 0, "open", "hh.exe", ThisWorkbook.Path & "1.chm", "", 1
Case 113
If MsgBox("Backup now", vbQuestion + vbYesNo) = vbYes Then BackUp
Case 114
Col = 3
SearchD
Case 115
showexcel
Case 116
readonly
e.c.t.

Instead of that, if i place code in a private sub test() just call test in keydown event

View Replies!   View Related
Writing Function For Textbox Keypress Event
I have got some code that is duplacated a few times. It allows only number values to be entered into a textbox.

Is there a way to make a function out of this to cut out the duplication of code.

View Replies!   View Related
Tab Key Change Event
I have a user input box (VBA) with two columns of data entry. The leftmost column has text boxes labeled color1, color2, color3, etc.. The rightmost column has text boxes labeled tag1, tag2, tag3, etc..

I have the TAB sequence set to go from color1 to tag1, color2 to tag2, color3to tag3, etc..

A user can inadvertently tab over the color1 (or color2, etc. columns) into the tag1 (or tag 2, etc. columns) column without entering data in the color column.

Is there coding to allow a TAB key entry to be a Change Event such that, if a user TABS out of color1 without entering data, a MsgBox could signal that they must enter data in the color1 field before they can continue?

Alternately, can you suggest a different approach? The goal is to require an entry in the leftmost column (color1) before they can proceed to the tag1 field. Of course, they are given a "Cancel" option.

View Replies!   View Related
Beep From KeyPress
I've got a small - almost "cosmetic" problem. I have a listBox on a userForm and I'm checking for a keystroke combination to see if the user wants to edit related data. That's working fine. However, when I return to the userForm, it beeps at me. Impertinent! I think it's because Excel doesn't know what to do with the keyStroke that I used - Alt U. I've attached code. I think that I just have to set the keycode to something that will be ignored by Excel.

View Replies!   View Related
Limit KeyPress To Text Only
Iv found some code that will limit my textbox entry to numbers only, i dont quite understand how it works and i need to change it so it limits the textbox keypress entry to Text Values only.

View Replies!   View Related
Get 2 Textboxes To Display Different Strings Based On Keypress
I have a Userform whose purpose it is to capture payment information, in particular Creditcard information. To this end it has (amongst other elements) 2 textboxes - textboxCardNr1 and TextboxCardnr2.

the User enters the cardnr in Textboxcardnr1 as, e.g. 4321098765432109. Through the code below I transform this into the more readable number
4321-0987-6543-2109. I capture the keypress instance to only allow numbers to be entered in this field and to simultaniously update TextboxCardNr2 with the keystrokes.

On exiting TextboxCardNr1 the content of TextboxCardNr2 is copied to the clipboard to be available for pasting on a secure website in the approriate field.

My problem is, that when the user makes a mistake and changes the number in TextboxCardNr1, those changes are not automatically mirrored in TextboxCardNr2.

I want the easy to read form of the card - with the dashes, to be saved. The website the user is pasting the content of TextboxCardnr2 to does not accept the number with dashes.

Private Sub TextBoxCardNr1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim taste As String
Dim wert As String

taste = VBA.Chr(KeyAscii)
wert = Me.TextBoxCardNr1.Text

KeyAscii = 0
If taste = "0" Or taste = "1" Or taste = "2" Or taste = "3" Or taste = "4" Or taste = "5" Or taste = "6" Or taste = "7" Or taste = "8" Or taste = "9" Then If Not Me.TextBoxCardNr2.Text Like "################" Then
Me.TextBoxCardNr2.Text = Me.TextBoxCardNr2.Text & taste............................

View Replies!   View Related
SetFocus On TextBox
I have the below to check for a numeric entry and when it's not, it deletes the entry, shows a Splash Form for a few seconds telling the user to enter Numeric, and then unloads.

The problem is, on the SetFocus part. I thought that it should put the cursor back into TextBox2 for another entry. It doesn't, in fact the cursor is nowhere to be seen. I have to grab the mouse and click in the TB2 to reenter a number.

Private Sub TextBox2_Change()
'//Check for numeric entry
If Not IsNumeric(TextBox2) Then
Me.TextBox2 = ""
'//Show SplashForm if not numeric
SplashForm.Show
Me.TextBox2.SetFocus
End If

End Sub

View Replies!   View Related
Setfocus In Userform Not Working
I'm having some problems with SetFocus on a user form that I am using. Code is Below.

Dim Answer As Variant
With cboAccount
If .Value "" And .ListIndex = -1 Then
Answer = MsgBox(cboAccount.Value & " is not a registered account, would you like to add it?", vbYesNo)
If Answer = vbYes Then
Load frmNewAccount
frmNewAccount.txtAccountName.Value = frmEnterTransaction.cboAccount.Value
frmNewAccount.Show
If Answer = vbNo Then
cboAccount.SetFocus
End If
End If
End If
End With
End Sub

basically the code asks if the if the answer placed in a cboAccount (combo box) is valid against the list designated to that combo box,

If the entry is not valid a message box appears asking if they would like to add the entry to the valid list if they do not want to do this they can click no and in which case I wan't the focus to be set back to the combo box however currently the setfocus command above does not does not work and the focus is set to the next text box.


View Replies!   View Related
Control Events And SetFocus
I have been working for 8-1/2 hours to get the focus to move to a specified control on my user form if criteria are met in two other controls on the same user form. It's not responding as I expect. I think I know what is happening; I just don't know why.

Situation: I have a user form with the following relevant fields in this tab order - LMonthDay, tbMonthDay, SBMonthDay, LYear, tbYear, SBYear, LTime, and tbTime. I used the designations L, tb, and SB in my control names to represent label, text box, and spin button respectively.

Problem: Once the SBMonthDay control has the focus, if I press TAB without changing the control's value, it does not execute the Exit event statement the way I anticipated. I want the focus to move to the tbTime control when the tbMonthDay & tbYear controls are already properly populated. (They will already be populated for 99% of the entries in this case.)

Private Sub SBMonthDay_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Debug.Print "SBMonthDay_Exit Begin"
If ISLIKE(tbMonthDay.text, "####") _
And ISLIKE(tbYear.text, "####") Then
tbTime.SetFocus
Else: tbYear.SetFocus
End If
Debug.Print "SBMonthDay_Exit End"
End Sub ...........................................

View Replies!   View Related
TextBox.SetFocus Not Working
if i write the following function on a checkbox,

View Replies!   View Related
Setfocus To Combobox On Multipage
I have a userform with a multipage consisting of 4 tabs.

Each one has several combobox's and 1 commandbutton.

As you move through the form the commandbutton moves the data to my worksheet then moves you to the next page. When it reaches the last page it will return you to the first page then unload the form which is great.

The problem I am having is the cursor is not in any of the boxes on the first page. If I click on one of the other tabs then return to first page the first combobox has the focus. All the tab orders are correct. Can anyone point me in the right direction.

Private Sub cmdNext3_Click()
ActiveWorkbook.Sheets("CarDetails").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

View Replies!   View Related
Using SetFocus To Any Other Object On Userform
I have been trying to setfocus to any other object on my userform with absolutely no luck at all?

In the code below I open a dialog box to import a text file (with CommandButton1), if no file is selected the user is prompted, this works fine. However it leaves focus to the textbox and the user would have to click on some other object and re-enter the textbox to open the dialog box again.

View Replies!   View Related
Setfocus + Show A Page
Excel 2003. User form coding an If statement the "Else" FieldName.Setfocus. The User form has a MultiPage object in addition to the above (setfocus) in a particular field (NOT on the MultiPage object) I want it open to Page2 instead of what ever page it was last closed on. I tried this: MultiPage1.Page2.Enabled

View Replies!   View Related
TextBox Setfocus Unexpected Call
I've got a user form with 12 textbox, one for each month a total textbox number 13 and a 14th text box (TB28) to enter $'s in.

AfterUpdate in each textbox 1-12 posts its value to a worksheet all values are summed in a =sum range which in turn populates textbox 13. When TB13.text = 100.00 it sucessfully calls a number of routines but what I can't get it to do is set focus on TB28.

Private Sub TextBox13_Change()
If TextBox13.Text = "100.00" Then
With FormPhasedAmt.TextBox28
.SelStart = 0
.SelLength = Len(TextBox28.Text)
.SetFocus
End With
End If
End Sub

It returns a Run time error Unexpected call to method or property access highlighting .SetFocus. The rest of the code works OK. Is it because other textbox on the same user form have text highlighted because of the tab order?

View Replies!   View Related
SetFocus On TextBox As First Control In A Frame
In Excel 2000 there seems to be a problem with setting the focus in a UserForm TextBox if the SetFocus method is applied in the same submodule as the UserForm.Show method. Microsoft's circumvention for this is to put the SetFocus command in the form's Activate event submodule.

This seems to work OK except when the TextBox you are applying the SetFocus to is the first control inside a frame. It works if the TextBox is not the first control, and it can be circumvented by first setting the focus on a subsequent control then switching the focus to the intended control.

However, the circumvention is not that useful if there is only one TextBox control in a frame or, as in my current project, if you try to create a generic piece of code to validate controls from multiple forms and set the focus from within the generic code.

Does anyone know of a way over overcoming this problem and being able to directly set the focus to the first TextBox inside a frame?

View Replies!   View Related
Setfocus (point To A Cell In A Spreadsheet)
I would like point to a cell in a spreadsheet, click a Command Button and copy the content of this cell to another cell, say D30.

View Replies!   View Related
Setfocus From Module To Userform Control
I have a project with many textboxes on different forms. On some of the textbox_exits on some of the forms, I call a standard module that checks what the user has input. If the user needs to change the input, a msgbox appears to inform the user the info needs to be changed.

I have tried to use ControlSource, Name etc., But I cannot seem to setfocus back onto which ever textbox the input needs to be changed. I think it has something to do wih the _exit event, but not sure.

View Replies!   View Related
SetFocus On Multi Page UserForm
I've got a userform with multiple pages, and in my userform initialize sub I set the focus to "combobox1" on "page1". When a user is on page2 of the form and clicks my "ClearData" button, which calls the initialize event, I get a run time error 2110 - "Can't move focus... etc" I would like users to have the ability to clear the form and start over no matter what page they are on.

View Replies!   View Related
SumProduct Formula (flow Data From The Download Tab To The Disb08 Tab Based On The Date)
I need to flow data from the Download tab to the Disb08 tab based on the date, that column F of the Download tab = "Outgoing Money Transfer" and that the 4 digit number in row 2 of the Disb08 tab is matched against Column G in the Download tab. When all those match, then the value from column J of the download tab would flow over.

View Replies!   View Related
VBA -Determine Last Row Of Source Data Tab For Use In Executing Formulas In Template Tab
I am currently building a workbook with 2 tabs.

The first is a data dump tab. The number of rows used in this tab will change each time it is used.

The second tab currently has a header row and in cells A2-H2 are formulas based on data in the first tab.

I am creating a macro to autofill those formulas, but I don't know how to determine what is the last row of data in the 1st tab and use that as reference in my macro.

View Replies!   View Related
Add To A Current Macro Which Currently Copies The Master Tab And Creates A New Tab With Tomorrow's Date On It
I need to add to a current macro which currently copies the master tab and creates a new tab with tomorrow's date on it. For reference here is the current macro:

View Replies!   View Related
Put Geometric Averages Of Each Column From Each Tab In A Summary Tab
I have multiple sheets each containing multiple columns of numbers, and sometimes when the numbers are not present there is a text error string such as "#N/A N.A.", or "#N/A N Ap". An example of the data is as follows:

4.06
1.28
3.411
#N/A N.A.
2.627
1.157
4.9
4.9
2.963

I would like to put geometric averages of each column from each tab in a summary tab.

* I tried using =GEOMEAN(...), but it can not handle the quantity of data that I have nor can it take non-numeric fields.

* Also Tried something like this =EXP(AVERAGE(IF(ISNUMBER(C:C),LN(C:C)))) as an array, but unless cells are specified explicitly, ie c10:c300, I get an answer of 1. I can not specify the cells because I have a variable height of each column.

View Replies!   View Related
VLOOKUP & IF With Dates (tab To Get Information From The Other Tab)
I have 2 Tabs.
1) Furnaces --> INPUT
2) Report --> OUTPUT

1) I have this speadsheet and every day i entry new information:
Date Furnace# Burner
6/1/2009F-11
6/1/2009F-513
6/1/2009F-512
6/2/2009F-16
6/3/2009F-15
6/4/2009F-14
6/4/2009F-514
6/5/2009F-511
6/6/2009F-12
6/7/2009F-11

2) I want in this tab to get information from the other tab (Furnaces) in order to have an every day control.

Furnace #Burner 6/1/2009 6/2/2009 6/3/2009 6/4/2009.. etc
F-1 1
2
3
4.............................

View Replies!   View Related
Inscribing Cells: Run A Macro On Enter Keypress, That Would Execute Different Code Depending On That "inscription" That Would Be Invisible To User
Is there any way to "Inscribe" a cell? I would like to run a macro on Enter keypress, that would execute different code depending on that "inscription" that would be invisible to user. I could use some properties of . Validation property like this:

Private Sub EnterPressed
'following code to ensure proper functioning of Enter in any other Worksheet
If ActiveSheet <> mySheet 'MySheet is global Variable then
ActiveCell.Offset(1,0).Select
exit Sub
End If
'now the real code
If ActiveCell.Validation.InputMessage = "1" Then
ActiveCell.Offset(0,1).Select
Else
'something else
End If
End Sub

The problem is, I use Data Validation and Conditional Formatting, so can't use any of these properties.

View Replies!   View Related
Worksheet Change Event :: (ByVal Target As Range) Event In Module After Creating A Sheet
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?

View Replies!   View Related
Change Event Causing Event To Fire Again
I have the following code, which works perfectly:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TotalDays As Integer

TotalDays = Range("C65536").End(xlUp).Row + 1

The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.

However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.

How do I add the following, to my previous code?

Range("E8").Value = Cells(7, 6) * 2.5

View Replies!   View Related
Second Tab Is Inventory Numbers And The First Tab Has My Inventory Items
I have a workbook. The second tab is inventory numbers and the first tab has my inventory items. I scan in the inventory number (unique) and it adds it to my inventory sheet. As I use inventory I scan the barcode and it inputs the serial number into my used inventory tab. What I need to do it when I scan the serial number for used inventory into the used inventory sheet to have it go to the inventory sheet and delete the line for that serial number.

View Replies!   View Related
Sumif (compare Column A On Tab 2 With Column A On Tab 1)
I have a spreadsheet that lists something like this (an easy version of it anyway!)

On tab 1 of my spreadsheet I have a table with lots of data similar to:

Column A Column B
111 12.23
111 14.32
222 48.23
222 28.34
222 10.23
222 33.40
333 23.21

On tab 2 of my spreadsheet I have unique versions of the data Column A on tab 1, for instance:

Column A
111
222
333

I would like to have a formula on tab 2 that can compare column A on tab 2 with column A on tab 1 - if it is similar I want it to total the amounts for all of the values equal.

So for instance, on tab 2 the final data should be this in my example:

Column A Column B
111 26.55
222 120.20
333 23.21

I've seen formulas that are similar.. but the condition is it can only total the rows that match. And preferably can do this from the other sheet.

View Replies!   View Related
IFSUM Tab
hi everyone, i have a formula in my spreadsheet that gets data from a worksheet called Graph Data,I have other worksheets that i would like to get data from by means of a drop down menu (6 sheets in all) the drop down link cell returns values of 1-6 and i have called the other worksheets simply 1 to 6, if i type 5 in place of Graph data then i get the required results so what i wanted to do was have the dropdown box link cell value dictate the worksheet tab in the formula below,i cant place the value in a cell (say A29) and then put A29 in place of graph data because it then looks for a tab called A29,i was wondering if this was possible to do and if anyone can help,many thanks,ive only just started on excel so im not that literate,ty

=IF(L60="Y",SUMIF('Graph Data'!$B:$B,L61,'Graph Data'!$C:$C)+SUMIF('Graph Data'!$B:$B,L61,'Graph Data'!$D:$D),"")

View Replies!   View Related
Copy A Tab
How can I copy a tab and then change the link on it to point to a another new tab in stend of the orginal tab.

View Replies!   View Related
Hide A Tab
is there a macro or bit of VBA code that can hide a worksheet tab when criteria in an if function is true???


View Replies!   View Related
Showing Any Changes On Tab
I have a tab within a workbook where i want the user to copy a different spreadsheet into and then press a macro button to run some code, however after this button has been pressed i want this one tab to show any changes that have been made to it, i.e make the cell red or something just so i can keep track of manual adjustemnts.


View Replies!   View Related
Naming The Tab
on sheet 1 which is named front cover i want to type in cell C8 a number or a name and it will change sheet 2 tab to what i type i have 20 sheets to do
i can get it working in b5 on same sheet
but i need it to work from the front cover sheet

View Replies!   View Related
Tab Naming
My workbook has a summary page and one page for every day of the month. I am making a template file, so I can duplicate it to use every month of the year.

Each week is separated by a worksheet titled "week 1", "week 2" , etc. This is used in the summary page so I can gather totals for a week.

What I want to be able to do is this:

On the first day of the new month, go to week one and type in the date. Then that sheet tab would equal the date, and each daily tab name thereafter would increment the appropriate amount (1day). Then on week 2, it would be equal to the tab value for Friday +3.

The problems I see are:

1. In week 1 the month can start on any on of the 5 worksheets I have set up to cover the week. So any first day coding I need may have to be repeated in the first 5 worksheets

Is this do-able?
2. Tab naming code I have found on this site is not working for dates. 9-15-08 typed into a1 will not translate into the tab, even tho it is a valid name. I am using code VoGII gave which follows: ...

View Replies!   View Related
Tab Name In Cell
Is there a function to return a Tab Name in a cell?

View Replies!   View Related
Summary Tab
i need a macro that can copy info from all tabs (30 tabs) (B1:ZZ500) and put them into a sumamry tab.

View Replies!   View Related
Deleting A Tab
i have created a user from called "delete"

from this the plan is that you will be able to select the name of the tab that you wish to delete from a drop down list (which will contain all the tab names) and click on a command button and this will then delete it

View Replies!   View Related
How Do I Name A Tab With VBA
I need to have a script create a new sheet, and name it the same as a value in a cell (named range). How might this be done?

View Replies!   View Related
Tab Properties
Within my code I have restricted the toolbar options that a user can access (i.e. for Menu Option 'Edit''Tools'):

Set myCmd = CommandBars("Worksheet menu bar").Controls("Edit")
myCmd.Controls("Delete Sheet").Enabled = False

But if the user wishes to delete the sheet, they can select the specific WorkSheet 'Tab' and Right-Click to Insert/Delete/Rename the sheet etc.

How do 'hide' these options within VBA? Or is there a Menu setting that I can be set to Enabled = False?

View Replies!   View Related
Tab Between Controls
I created a form using multiple text boxes and combo-boxes using Excel VBA controls but do not know how to enable the ability to tab between those boxes. "Auto Tab" doesn't seem to do it.

View Replies!   View Related
Renaming A Tab In A Workbook
I have several tabs in a workbook that need different names depending on what is selected from a drop down menu on one of the sheets - "Staff Details" sheet. Any clever persons out there able to help?

View Replies!   View Related
Fetching Formulas From Another Tab
In the first tab, I have different values for:

1)workdays
2)Saturdays
3)Sundays,

for every 0,5h.

In the second tab, I have a list with every 0,5h for one year. Now I'm trying to make this tab fetch the values from the first tab.

Thus, for instance for 2006:

*The year starts with a Sunday, so all the corresponding values for Sunday are fetched.
*Then it just goes on: fetching 5xworkday+Saturday+Sunday and so on.


View Replies!   View Related
Multiple Tab Formula
When producing a summary sheet that feeds off several tabs, is there a way I could drag the formula accross with it selecting data from the same cell in multiple tabs?

View Replies!   View Related
Copy Cells In Other Tab
On tab A when a user is on a cell, I want to copy that cell to tab B. Based on the cell it needs to perform a number of copy actions (see below). Copy is done based on the selected cell and activating the copy macro I want to create.

E.g. if cell A8 (variabel) is selected (on tab A) it needs to copy A7(name of employee, fixed) and H5 (date, fixed) to tab B.

On the tab B corresponding colums are (first cell names are from Tab A) A8 = C5, A7 = B5, H5 = A5 and then create a new line for the next copy action.


View Replies!   View Related
Tab Between Worksheet Comboboxes
I'm sure this must have been answered before but I cant find it, sorry.....

I have 12 comboboxes on a worksheet. They are normally filled out by typing the first 3 characters of one of the entries in the list they are populated with.

I.e. the list starts;

AAC - Al Arish - Egypt - HEAR
AAN - Al Ain - United Arab Emirates - OMAN
AAW - Abottabad - Pakistan -

The first 3 character are unique to each line.

I'd like to be able to enter the 3 characters in the first combobox & then tab to the 2nd combobox, etc, etc. No problem in a userform but how on earth do I do that when the comboboxes are on the worksheet?


View Replies!   View Related
Tab To Work Automatically
Is there any way that Tab should automatically jump to next column, after the entry has been made.

I have only one number (one digit) per column. I wish that when I enter the digit the tab should go automatically to next column. And also if it is possible that tab may miss the blank column and go to the next where the entry is to be made.

View Replies!   View Related
Can Sheet Tab = Cell
Is there a way for the sheet tab to equal a cell. If I put a name in a cell, say "B3", can I get it to load as the sheet tab name or the opposite way.

View Replies!   View Related
Automatic Tab Color
I am trying to create a program that when a cell changes to a specific text from a pulldown menu of two items the active sheet's tab changes color depending on what is selected. Is this possible using the pull down menu, and how would you go about doing this?

View Replies!   View Related
Prevent Tab Switching
I made up a small subprocedure that searches through a large list of zipcodes (These zipcodes are in a separate sheet). Whenever I execute my subprocedure in another sheet, it jumps to the zipcode sheet and then goes back to where I originally executed the subprocedure.

Is there any way that I can prevent the sheets from switching back and forth?

View Replies!   View Related
Tab Name To Show In Cell
How can I get the Tab name to show up in a cell?

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