Is there a simple way to trap mouse and keyboard events in Excel, only within a VB loop, and then return to normal behavior after exiting the loop? That is, I want to know if a key is pressed (and what key) or the mouse clicked and not have it affect the spreadsheet. I don't want the key pressed to type into the spreadsheet.
View 1 Replies
For instance if I want to cut a row and column, how do I insert with the keyboard without pasting over other data
View 9 Replies View Relatedis there a keyboard shortcut to select a row and delete it without using your mouse?
select a row and delete it without using your mouse?
I have a worksheet that serves as a navigation page for the workbook and use the following code to restrict from scrolling off the viewable content. The problem is that the code doesn't kick in until either a click if the mouse or keyboard input. This initially leaves the worksheet open for scrolling until input is received.
How do I make this code activate when the wb is loaded? Code in the Thisworkbook module already makes the menu the visible sheet, and code limiting the scroll area is in the sheet module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.ScrollArea = "A1:L11"
End With
Range("A1").Activate
End Sub
I've also tried using "Range("A1").Select", but it doesn't work either. How can I get this to work correctly?
I'm using Excel 2007 and I have a reasonable level of experience with VBA coding in normal / object modules, but I have no experience with class modules or events ... so I do not have a good command on what goes into a normal module vice a class module if I want to use mouse events to rotate a chart.
I am looking to rotate a chart ... Since I am not allowed to post a file, I will give the 3D array here:
1.0000
2.0000
3.0000
4.0000
[Code]...
I have placed into a normal module the following code (based on what I was able to pick up from MSDN):
Code:
Dim myClassModule As New EventClassModule
Sub InitializeChart()
Set myClassModule.myChartClass = Worksheets(1).ChartObjects(1).Chart
End Sub
And I have placed the following code into a class module named EventClassModule (also based on what I was able to pick up from MSDN):
Code:
Public WithEvents myChartClass As Chart
I want to rotate the chart by using click and drag, and, if possible, I'd also like to be able to zoom in and out via some method (say a mouse wheel event).
In the following link, a guy has done a beautiful job of enabling scatter plot rotation using scroll bars, but I'd like to be able to use mouse events instead:
3d scatter plot for MS Excel (VBA macro)
I have a UserForm with a Text Box, I populate that Text Box with a number (say 5) and then the following code runs:
Code:
Private Sub tbOverrideMokWh_Change()
Application.EnableEvents = False: Application.ScreenUpdating = False
With tbOverrideMokWh
[Code]....
After the Sub is run 1 time, it runs again. Why? I've disabled Events?
I have assigned some value to the id of the some cell.
now i have cut the cell and pastes into some other cell.
now the situation is id value is same for both the cell(cut and pasted).
As soon as i cut i would like to assign some new id value programtically to the cell which is being cut.
How would I trap the escape key being pushed while in a comment box?
In case user accidentally modifies comments box and they do not wish to save the changes. Pushing escape will save changes, which I do not wish to do.
This thread is closest code I found, but I do not know how to adapt it for the comment box.
Errror trapping with Application.Dialogs(xlDialogOpen).Show
When the user scrolls the worksheet (using a scroll wheel, or the scroll bars) I would like to have a VBA-code triggered, but in Excel 2003 there is no "VBA-event" for scrolling...
Is there any way of trapping that a scroll has happened, even if the selection didn't change?
The error trap does work when there is an error, but it also runs at the end of the code when there isn't an error... I'm not sure what I've done wrong. When I test with data that is correct, it still runs through the code when it gets to the bottom. Have I put the error trap in the wrong place?
I have put in bold the section of the code with the error trap.
Option Explicit
Const TargetDB = "Group Life Pricing Tables v0.1.accdb"
Sub HMUK_Val()
Application.ScreenUpdating = False
'define variables to be used in the process
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String
Dim SchemeInfo As Integer
Dim CurrentAge As Integer
Dim CurrentRow As Integer
Dim CurrentSex As String
Dim Message
'check that Scheme data has already been entered
For SchemeInfo = 1 To 13
If Sheets("Working Info").Cells(SchemeInfo, 2).Value = "" Then
Message = MsgBox("Please ensure all Scheme information has been entered before processing member info", vbOKOnly, "Warning")
Exit Sub
End If
Next SchemeInfo
I am trying to find some solution on frequent errors , that come up when one copy, or drag formulae.
Sometime the error doesn’t appear on surface, but after digging you realize that some formula is not pointing to correct
cells.
I want to create a macro which will do following things :
1. It will go to each cell in the selected range, and compare the cell’s formula with that of the cell on its right and left.
2. If the formulae are not similar it should change the color of that cell.
Basically, I am trying to catch logical errors. However I don’t know how to approach this problem? Is there any in-built functionality in Excel that can do this task ?
I am aware that I can use single changing events in worksheet change events. For instance, if column 1, or A is changed, do something. This is only a single If statement, i.e. either the condition is true, or not. What I am not sure is if I can use two changing events, i.e. two conditions. For e.g. I would like if Column A value is X and Column B is "Active", action it, but only if two conditions are true.
For.e,g. The below syntax does not work. If it is only column A, it does work, but I want both A and B to be true, then copy and paste the target does not anything.
VB:
If Target.Column = 1 Then
If Target.Column = 2 Then
If Not Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then
If Not Intersect(Target, Range("B2:B" & Rows.Count)) Is Nothing Then
If Target.Value = "X" And Target.Value = "Active" Then
I am using the calendar control on a form of mine, which i believe requires ms access to be installed to use(from what ive read). This is fine as most of the PC's it will be run on have this installed. However if it is run on a PC that doesnt have ms access it gives me the "Could not load an object because it is not avaliable on this machine" and then "cant find project or library error". How can I trap this error ? Ive tries whe the workbook opens, and when the form initializes but it just brings up that error
View 2 Replies View RelatedI have a user form with a TextBox (TextBox1), the user enters a filename and presses CommandButton1, this then opens the file. I have set an error trap so that if the file does not exist a MsgBox prompts the user to re-enter. So far so good, however if the error trap operates any further files entered are declared as non-existent even if I know they exist. I thought this would be quite easy but once again VBA bites me back!!
here is my code
Sub CommandButton1_Click()
stPath = ThisWorkbook.Path
stFilename = "Retention" & "" & TextBox1 & ".xls"
stFullname = stPath & "" & stFilename 'completes full path of for TextBox1
On Error Goto Errorhandler: 'go to this if file does not exist
Workbooks.Open stFullname
ActiveWorkbook.RunAutoMacros xlAutoOpen
I am using a TreeView control and need to determine if the user clicked on the Image or the Label of a node. The Hittest function can return either a TreeViewHitTestInfo object or a node. By default it returns the node. I'm not quite sure how to trigger the return of a TreeViewHittestInfo object, but at the moment I'm even struggling to make VBA aware of that class. A simple statement like
Dim info As TreeViewHitTestInfo
returns an error saying that TreeViewHitTestInfo is not defined.
Workbook A has VBA code. The VBA code opens a collection of 2000 workbooks. The workbooks are processed one at a time, with Workbook A opening Workbook B, do something, close Workbook B, get next workbook. Sometimes Workbook B has some kind of access issue that results in:
"[File Name] cannot be accessed. The file may
be read-only, or you may be trying to
access a read-only location. Or, the
server the document is stored on may
not be responding.
RETRY ... CANCEL"
If RETRY is clicked, the process returns the same message. If CANCEL is clicked, the process is crashed, Excel is closed, and all data processed is lost. What is the VBA syntax to programmatically trap this condition so it can be processed in an orderly manner?
Am using a email notification macro but want to error trap that "a program is trying to send an email on your behalf" thing. What i would like to do is if the user says "no" then a Retry/Cancel msgbox comes up with the prompt "Please select yes to enable a submission notification to be sent on your behalf" then if they select retry, the macro is re-run, if they select cancel, then only the latter portion of the macro is run.
View 3 Replies View RelatedI've programmed in many different forms of BASIC in my days, but I'm having too much trouble with VBA, and can't figure out if it's me or if it's the version of Excel I'm using (2002). I wrote a program in Excel, where if certain cells contain values other cell's calculations will become active. I want to click on a button that I created on the spreadsheet, then be able to select different keyboard strokes to activate these cells. I understand that there are 3 event codes that circle around this concept and that is KeyDown, KeyUp and KeyPress. Whenever I type in what MS Help told me to type in for these events, it always returns a runtime error.
View 9 Replies View RelatedDoes anyone know if there is anyway that we can command Excel to avoid accepting aNY KEYBOARD ENTRIES? what intend to do is restrict a certain region on a worksheet for Users not to be able to type anything but but there are commandbuttons on the header which use: Range("C65536").End(xlUp)(2, 1) = "Value for the last cell" code to get the input into the cell. The purpose is to make use of the cmdbuttons with mouse but the data notto be keyed in !!!
View 2 Replies View Relatedis there any way to copy and paste "value only" using keyboard? Ctrl+V is pasting everything. i want existing format to remain the same and using mouse is taking lot of time :
View 4 Replies View RelatedI've run into a problem and I cannot resolve. I am designing an Excel Application in VBA. During the execution of the VBA code, the user will press different keys on the keyboard. These keystrokes are determined by using the GetAsyncKeyState Function. The problem happens when I exit from the VBA code and move back to a worksheet outside the code execution.
Lets say a user has pressed the 'Q' and 'X' key just prior to the final VBA subroutine running and releasing the worksheets back to the user. On the worksheet, in the selected cell will appear the 'Q' and 'X'. For some reason these keystrokes are not 'flushed' from the keyboard buffer prior to the code completion and are being transfered to the active worksheet. This is really annoying if I am testing the code in the VBA editor, because the spurious keystrokes will be placed in my VBA code, causing me to search for the out of place characters.
keyboard shortcut to insert a row.
View 9 Replies View RelatedMy ENTER button in my keyboard does not work.
I think it is because I have disable it.
does anybody know hot to enableit?
kind of:
Application.OnKey "^"
but don't know which one!
Some expert users can unhide a sheet in a worksheet which is protected by password. But I don't want to place a button in the worksheet for everyone to see and wonder what it's for. I can record a macro and assign a keyboard shortcut but I can't do it it with existing macros ?
View 3 Replies View RelatedI would like to be able to check a checkbox on a userform with a keyboard stroke rather than using the mouse. Any key will do, "P" or Enter would be prefered. It would make data entry much easier.
View 4 Replies View RelatedI have been working on a workbook, and all of a sudden, my keyboard no longer worked in Excel. No shortcuts, no CTRL+C, no CTRL+V, no CTRL+S, no CTRL+A, no F12 to save, no letters, no numbers.... The only button that is working is my ALT key, and I use the other letter keys just fine to navigate after pressing ALT.... But that's it!
I have still been able to work on it because I can still write in Word, and I can still write more code in the Visual Basic window, and I can still click the buttons I create under the Developer... but it's still frustrating to not be able to type a thing in any of the cells, formula bar, or be able to use any of the shortcuts. And now when I tried to close it, I can still select everything else, and I can close this workbook, but it won't close Excel.
I would like to use Shapes in excel, instead using userform as the code below.In this userform, it has command buttons for the numbers and the a text box to show the numbers.How can I do it, using square shapes for numbers and another rectangle shape, to show the written numbers.I would like to create a number keyboard using "shapes".
[Code]....
My macro essentially requires a deletion of the entire sheet, so I cannot run it through a button, it'd by definition be deleted. So I was thinking to run this macro through the keyboard, how to assign a keyboard key to existing macro?
View 4 Replies View RelatedI need keyboard shortcut for switching through worksheets.
For now, I have this VBA macro and shortcut Ctrl+R:
How can I create keyboard shortcuts in the easiest way.
E.g.: I would like to create my own shortcut for "Merge".
