Disable keyboard or typed commands

[UnsolvedCoding]

I was talking with some of my co-workers about how to keep people from making mistakes while certain parts of code run and several suggestions came up that I am curious about.

There were two specific that I wanted to get feed back on because of the limited number of times they would be used and because of concerns about errors occuring.

These concerns were specifically brought up because people will start the Excel program and rather then waiting for it to finish will switch to other applications. For example, start Excel and then flip to Outlook.

The options that came up are:

- lock out the keyboard during some procedures.

- disable the bulk of the keys on the keyboard, leaving only the escape key functional.

To me, these are both answers and problems.

What would be the general thinking on how to limit errors due to user input while the code is running?

 

[Andrzejek]

people will start the Excel program and rather then waiting for it to finish will switch to other applications

WHY would they need to wait for Excel program to finish before they would switch to another application?

But I am with you - both 'solutions' are terrible.

Have fun.

---- Andy

 

[UnsolvedCoding]

Its not that they need to wait so much as we have concerns about someone doing something during one of the more sensative times that would screw up the database.

 

[Andrzejek]

"screw up the database" - you cannot blame it on the user. It is - I am sad to say - a programmer's fault. IMHO :-(
And if going from Excel to Outlook (or anything else) messes up your data base, there is something wrong with the program, not with the user.

Have fun.

---- Andy

 

[strongm]

I'm with Andy on this

 

[UnsolvedCoding]

Perhaps in theory thats true. On the other hand, its fairly easy to corrupt data in Excel but bumping a keyboard or by typing at a wrong time.

 

[Andrzejek]

And that’s why we – programmers – make the big bucks (yeah, right...) to prevent users from entering wrong or bogus information and protect the data bases from ‘keyboard bumping’ accidents.

Have fun.

---- Andy

 

[strongm]

I'm not sure that I agree.

15 years working with finacial models in the investment banking and hedge fund markets suggest that Excel is surprisingly resilient (except shared sheets) - unless some poor assumptions are made during coding (and since much spreadsheet code is written by people who are not programmers, such assumptions do occur: one, for example, is the assumption that the currently selected range will not change whilst the code is running)

 

[PWD]

Good afternoon, I think I can see what the OP means though. For example I have inherited some code that opens workbooks and relies on the current (Active) workbook being the focus. For example,

Workbooks.Open cf_n 'Codeframe Workbook

ActiveWorkbook.Sheets(cf_s).Select 'Codeframe Sheet
On Error GoTo 0
With ActiveSheet
...
...
ActiveWorkbook.Close
...
...
Workbooks.Open in_n 'Input (Destination) WorkBook

End If 'If First_Time = True Then

Windows(in_n).Activate

ActiveWorkbook.Sheets(in_s).Select 'Input (Destination) Sheet
On Error GoTo 0
i = 1
With ActiveSheet
...
...

Would it be fair to assume that this could be tidied/made more secure by using

With whatever workbook

or something similar?

Many thanks,
D€$

 

[combo]

It's again programmer's fault. Much more secure:

Dim wbCodeframe as Workbook
Dim wsCodeframe as Worksheet

Set wbCodeframe = Workbooks.Open(cf_n)
Set wsCodeframe = wbCodeframe.Sheets(cf_s)

... and in most cases there is no need to select or activate anything to proceed with.

combo

 

[PWD]

Hmmm, OK thanks. I'll see what I can do.

Many thanks,
D€$

 

[PWD]

Just another question on this:- I don't understand (yet) what makes that

Much more secure.

I have amended the code as above, then changed

With ActiveSheet

to

 With wsCodeframe

and that all seems to be fine. I have changed

ActiveWorkbook.Close

to

 Workbooks(cf_n).Close SaveChanges:=False 'Codeframe Workbook

Previously I had

End If 'If First_Time = True Then

Windows(in_n).Activate

and have replace that with something that seems a bit clumsy;

On Error Resume Next
Set wbInput = Workbooks.Open(in_n)
If Err.Number <> 0 Then
Err.Clear      'Error
End If
On Error GoTo 0

Set wsInput = wbInput.Sheets(in_s) 'Codeframe Sheet
On Error GoTo 0
i = 1

With wsInput 'Input (Destination) Sheet

So, basically, I'd like to know if I'm on the right track (and why!) or if I'm going completely off the rails.

Many thanks,
D€$

 

[PWD]

OK, I've found something to replace what I referred to as

clumsy

If Not WorkbookOpen(in_n) Then
Set wbInput = Workbooks.Open(in_n)
End If

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

But now I'm thinking

Why do I need to do that check anymore?

I've declared

 Public wsInput As Worksheet

and have

Set wsInput = wbInput.Sheets(in_s) 'Codeframe Sheet

so there doesn't appear to be any reason why

With wsInput 'Input (Destination) Sheet

shouldn't just work, is there?

Many thanks,
D€$

 

[combo]

A couple of months ago I inherited a code that widely used ActiveWorkbook, ActiveSheet, .Select and Selection. Ii's hard to understand a logic of such code or the object in use, esp. when the user has to activate specific workbook and run macro. Another disadvantage is that you never know what the selection is. Typically such situation requires an extra validation that the active object is the one you need.
Generally is easier to assign an object to a variable and next use it:
you can:

ActiveWorkbook.Close

or

Workbooks(cf_n).Close SaveChanges:=False 'Codeframe Workbook

or

wbCodeframe.Close SaveChanges:=False 'Codeframe Workbook

Yhe error testing can be limited to checking object when setting the variable and, in case of possible user interaction, checking [tt]If ObjectVariable is Not Nothing[/tt]


combo