VBA connected to Checkboxes doesn't work - always proceeds only first option

[Katerina8457]

Hello, please could you advise how to connect checkboxes with VBA to adjust excel files.

I have excel files for 11 entities (countries).
I need to do adjustment sometimes on each of them or sometimes only chose which file should be adjusted.

I created checkboxes with labels:

 x  Select All
 x  country1
 x  country2
......
......
 x country11

I need Access VBA for open, save and close related excel files. The issue is that it always opens, saves and closes only file for first country and then states "debug".
Files names are the same as label captions connected with checkbox and ".xls" at the end.

Here is what I tried:

Dim LoopIndex As Integer
Set xlapp = CreateObject("Excel.Application")
xlapp.DisplayAlerts = False
filepath = "path to the file"

For LoopIndex = 1 To 11
Select Case Me.Controls("CB" & LoopIndex)
Case Me.Controls("CB" & LoopIndex) = True
xlapp.workbooks.Open filepath & Me.Controls("LB" & LoopIndex).Caption & ".xls"
xlapp.Visible = True
xlapp.ActiveWorkbook.Save
xlapp.ActiveWorkbook.Close
Set xlapp = Nothing
End Select

Next LoopIndex

Thanks for your advice in advance.

[jimatqsi]

Katerina8457,
Welcome to Bytes.com Please be sure to use code tags around any code you post by clicking the [CODE/] button and placing your code between the tags.

The Debug message means you have hit an error in the code. You should add some error handling to your code. Here's a generalized sample, from Allen Browne http://allenbrowne.com/ser-23a.html

1 Sub|Function SomeName()
2     On Error GoTo Err_SomeName          ' Initialize error handling.
3     ' Code to do something here.
4 Exit_SomeName:                          ' Label to resume after error.
5     Exit Sub|Function                   ' Exit before error handler.
6 Err_SomeName:                           ' Label to jump to on error.
7     MsgBox Err.Number & Err.Description ' Place error handling here.
8     Resume Exit_SomeName                ' Pick up again and quit.
9 End Sub|Function

I don't understand why it works at all if this is your actual code. Do you know what a file path is? It should be something like "C:\foldername\ filename.xls". Perhaps you are only using "path to the file" here to represent some path that is actually in your code.

It is not really appropriate to use Select Case to test True/False values. Normally you would just say

if Me.Controls("CB" & LoopIndex) =True then
   do stuff here
end if

since there's no need to test any other cases.

With the error handling you may get additional info about your error from the Msgbox.

Jim

[zmbd]

Katerina8457 - please check your bytes.com inbox.

[NeoPa]

Jim makes a good point about using If when there is only a single test, but spoils it somewhat by comparing a CheckBox (Boolean) to True. That is both unnecessary and potentially misleading in some cases (though not here). If you already have a boolean value then there is no need to compare it to anything in order to produce a boolean result.

[jimatqsi]

NP, I always considered that a matter of form, not substance. In this instance, I chose specifically to spell it out like that, in order to be crystal clear to someone who might be new to the language.

Please describe a case where that is potentially misleading. Is it the use of True/False vernacular vs Yes/No vs Checked/Unchecked ?

Thanks,
Jim

[zmbd]

True/False vernacular vs Yes/No vs Checked/Unchecked

True = -1 or 1 depending on flavor
False = 0

Yes IFF== vbYes = 6
No IFF== vbNo = 7

But note in the query table, these are nothing more than display formatting and the record field can be -1,0,1,null depending on how the properties are setup and if VBA is involved

control check
initial state unbound = null
initial state bound no record entry in field = null (maybe)
toggled once so that checked = -1
toggled again so that unchecked = 0
can be set in vba to = -1,0,1,null

null can, but does not always, branch to the false side in a logic test. Found this out the hard way with a Mac translation of the VBA code. I was depending on the default null value to test to the false side of the branch - OPPS, was testing as true and giving the user DBA rights - Now I explicitly set or test for all such controls to desired state starting with isNull()

so... perhaps not as straight forward as one would like.

[NeoPa]

Hi Jim.

The other, more common, confusion is that TRUE = -1 (in VBA and most languages with sense). This is because all bits are set. Boolean values are stored as signed integers (Integer in VBA) so they are never an exact match for a single bit boolean. FALSE = 0.

A side-effect of this is that there are a very number of Integer values which are neither. These are nevertheless treated as True by the compiler. By which I mean that any non-zero value causes the True branch to be taken.

This is fine, until people try to treat the value as a proper boolean (A variable that can have only two possible states.), which it is not.

Consider a value of X=32.

If X Then ...

This resolves to True. The ... part is executed as you'd expect. However, :

If X = True Then ...

This resolves to False. Numerically, 32 <> -1.

The only reliable way to compare values to produce a compatible boolean result is to compare them to False :

If X <> False Then ...

This always produces a result consistent with using X directly.

NB. While I applaud the idea of making the code easier to understand, in this case I believe that such a use will actually help people to keep hold of their flawed understanding of how things work. It only makes sense if you assume that booleans must be the result of a comparison. However, this is not the case. Booleans are so much more flexible than that. It's a real shame that a reliable implementation of the boolean concept was never provided for VBA. You're certainly not the first to fall over this one, and I doubt you'll be the last.