Breaking Code (VBA Excel 97)

Topic

New Reply

Is there any way to keep cycling through code until a key is pressed on the key board? i know u can press Esc to break code and then press End but i would like to exit the code when a certain button, Space for example, is pressed!

Thanks
Jamie

Reply | Quote

Replies

The only edit functions are what you see in the Debug menu (F5 Run, F9 Break, etc.) You’d have to write your own event handler to capture your desired keystroke and then call a function with Stop in it, but that would just cause it to stop at that routine of course. You could then use Control-L to see the call stack of where the code was.

HTH, Deb

Reply | Quote

Any idea’s on the code for an event handler? I really ain’t got a clue where to even start on this one! I did think of using the KeyDown/KeyPress Handlers but my main problem is getting the program to give control to the user, otherwise the KeyDown/KeyPress Event handlers don’t Run and that’s as far as i got! Another thing i thought about was clearing the keyboard buffer and then checking if anything had been entered in it and stoping the program if it had, i know this can be donw with other programing languages but i aint sure if it can be done in VBA nevermind how to do it! So on summing up – PLEASE HELP!

Reply | Quote

Attached is a sample workbook with how to use ESC to stop the code from running. It’s not an ideal situation but close enough. I added a call to my sub SetKeys which enables this in Workbook_Open. The three subs are in module Tools.

The logic is to assign code to the ESC key to stop execution and code to the ENTER key (on numeric pad) to disable this debug mode. You can use whatver keys you want of course. Once debugging is disabled (when you press ENTER key) I didn’t know how you wanted to re-enable it. Right now it just gets enabled at workbook open. I’ll leave that design decision to you.

Read the VBA help on the OnKey function.

Deb

Reply | Quote

Worked like a charm! Thanks Very Much Deb!

Reply | Quote

Hello. Below is a macro that scrolls left to right through a series of worksheets, starting with the number input in the input box. I’d like to hit a key to make it stop without getting kicked over to the de######. I’ve looked at your stuff, but I’m new to this, so I’m foggy. Can I get the for loop to quit without the de###### popping up?

Sub scroll()

‘
‘
‘ Keyboard Shortcut: Ctrl+n
‘
Dim shtnum As Integer
Dim sheetname As String
Dim numsheets As Integer
Dim scrolln As Integer
Dim sheetnum As Integer

‘
‘ Count the work sheets:
numsheets = Sheets.Count

sheetnum = InputBox(“Enter just the sheet number. Hit Esc, then End to stop.”)

For shtnum = sheetnum To numsheets ‘Sheet 1 was used to start the chart. See above.

sheetname = “sheet” & Format$(shtnum)
Worksheets(sheetname).Activate
‘ Sheets(sheetname).Select

‘ Freeze the first column:
Columns(“B:B”).Select
ActiveWindow.FreezePanes = True

‘ Scroll along to the right:

‘ Help info:
‘ Scrolls the contents of the window by rows or columns.

‘ expression.SmallScroll(Down, Up, ToRight, ToLeft)
‘ Any of the arguments can be a negative number.
‘ expression Required. An expression that returns a Window object.

For scrolln = 1 To 256 Step 1
ActiveWindow.SmallScroll ToRight:=6
Next
Next
End Sub

Reply | Quote

With a “normal” workbook and a modern PC this code should run so fast that you’d hardly have time to stop the code.
It might be better to ask the user for a first sheet number and a last sheet number, and then use something like

For shtnum = intFirstSheet To intLastSheet

Reply | Quote

Hans,

I’m not sure why, but the scrolling is slow enough to watch it go by, and even slow enough to focus on the cells. I have a lot of formatting, graphics, and a frozen pane. The purpose of the scrolling is to pick out places where I’d like to stop and look closer. In that case, I’d like to hit a key or key sequence to stop it right there, without having the de###### pop up. I could add a prompt for an ending worksheet, but I don’t necessarily know where I want to stop ahead of time.

Thanks,
Kent

Reply | Quote

I can’t get Jujuraf’s code to work. I’d use MsgBox within the loop to ask the user whether he wants to continue. it’s irritating, I know, but you only have to press Enter each time.

Reply | Quote

I’m not sure why you want to scroll each page all the way over to the right but ….

Since you only have 3 sheets in the sample workbook, you couldn’t interrupt it fast enough to test anyone hitting the ESC key. I took your code and dumped it in a module (see attached). I used an OnError handler to detect the ESC key. You could test it by adding a really long while loop to slow things down or add tons of sheets. It’s totally untested. I attached a VBA .bas file so just import it into your Excel workbook.

// Thnx, Deb

Reply | Quote

Deb,

All I can say is, THANKS!!!!!!! I pasted in your changes, and now, the scrolling just stops when I hit ESC, just like I wanted it to. If you’re curious, I have more than 50 worksheets with about 725 rows full on each, and the first column is frozen. The rest of the columns contain data in chronological order during a product test. I conditionally formatted the cells for different colors, depending on the data, so I can watch events unfold just by seeing the colors. Now, I can stop when I see something of interest, and restart at the current worksheet, or any other worksheet.

Very, very, nice!

Being new to this, do I need to do anything to officially close my request? I’m happy for the moment.
Kent

Reply | Quote

Woody’s Lounge doesn’t “close” threads (unless there’s a special reason for it). We do appreciate it that you have posted back to tell Deb and all others who read this thread that the problem has been solved!

Reply | Quote