Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Breaking Code (VBA Excel 97)

    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

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Code (VBA Excel 97)

    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

  3. #3
    New Lounger
    Join Date
    Oct 2001
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Code (VBA Excel 97)

    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!

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Code (VBA Excel 97)

    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. <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15> 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 <img src=/S/munch.gif border=0 alt=munch width=19 height=17>
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Oct 2001
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Code (VBA Excel 97)

    Worked like a charm! Thanks Very Much Deb!

  6. #6
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Code (VBA Excel 97)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Breaking Code (VBA Excel 97)

    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

  8. #8
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Code (VBA Excel 97)

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Breaking Code (VBA Excel 97)

    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.

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Code (VBA Excel 97)

    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
    Attached Files Attached Files

  11. #11
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking Code (VBA Excel 97)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Breaking Code (VBA Excel 97)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •