Excel 2003 checkbox 2

[hext2003]

Having some trouble using a checkbox. Tried both Form Field and Control Tool checkboxes

Code that is giving me trouble is

Case 1 'Amended

If Amended.Value = vbChecked Then
MyStr = MyStr & "Y"
Else
MyStr = MyStr & "N"
End If

This is a snipet of a large piece of code. There are 15 different case results. This code takes a bunch of fields from my spreadsheet concatenates them into a long text string (MyStr) then outputs this string to a text file. I have a control Tool Check box named Amended. I just need to insert a Y or N into my text string depending on if this box is checked or not. All of the rest of the cases work just fine. I just can't seem to GET the value of this checkbox.

Am I not declaring something? This results is always false! Regardless if checked or not.

TIA

 

[MarkWalsh]

Have you checked what the actual value of Amended.Value is in the debugger? I believe it's going to be a boolean True/False.

If Amended.Value Then
MyStr = MyStr & "Y"
Else
MyStr = MyStr & "N"
End If

 

[hext2003]

It's -4146

 

[Andrzejek]

If you use a UserForm in Excel 2003 with one checkbox named Amended and one commandbutton, this works like a dream:

Option Explicit

Private Sub CommandButton1_Click()
Dim MyStr As String

If Amended.Value Then
    MyStr = MyStr & "Y"
Else
    MyStr = MyStr & "N"
End If

Debug.Print MyStr

End Sub

Have fun.

---- Andy

 

[hext2003]

I was expecting it to be booleean. I have tried Amended.Value = True Amended.Value = 1

Before this line of code it shows Amended = empty

Now I am getting an "Object Required" error hmmmm

I've changed it so many times not sure which line actually compiled.

 

[hext2003]

Why do I need a command button in addition to the check box?

 

[hext2003]

I have a button on sheet 4 that triggers this code

the check box "Amended" is on Sheet 1

do I need to do something more to get the value for Amended?

 

[Gruuuu]

Are you certain that the checkbox object name is Amended? Not "Checkbox43" or something?

 

[hext2003]

I know this is killing me - it should be that simple right???

When I look at properties the name is Amended

When I click on view code it goes to
Private Sub Amended_Click()

unless I am missing another place I believe it is named Amended. I'm still wondering if I need to do Sheets("Sheet 1").Checkboxes("Amended") kinda thing???? or is that form fields? I have tried both form fields and control tool right now focusing on control tool as I thought that would be easiest.

 

[hext2003]

I created a

Sub GetAmended(ChkBool As Boolean)

ChkBool = Amended.Value

End Sub

function on the Sheet 1

Now I am calling this from Sheet 4 inside the code

Case 1 'Amended

ChkBool = Sheets("Pg1").GetAmended(ChkBool)
If ChkBool = True Then
MyStr = MyStr & "Y"
Else
MyStr = MyStr & "N"
End If

When I step thru the code I see the value as TRUE when on sheet 1 but it changes back to FALSE when the code returns to Sheet 4 Am I calling it wrong?

 

[Gruuuu]

Wait, where is your code located? Is it in its own module or in a Worksheet module, or the Workbook module?

 

[hext2003]

Finally GOT IT

I did

Case 1 'Amended
ChkBool = Sheets("Pg1").Amended.Value

If ChkBool = True Then
MyStr = MyStr & "Y"
Else
MyStr = MyStr & "N"
End If

I moved the Dim ChkBool as Boolean out of my function and declared it as a global - not sure if that was truely the fix or what - but it's working now!

Thx!

 

[Gruuuu]

Ah, yep, it's a scope thing.

Toss the Sub GetAmended()

Use Sheets(#).Amended.Value

If Sheets(1).Amended.Value Then
    'You Win
End If

 

[hext2003]

At first I had it in it's own Module... then I moved it onto Sheet 4

Why wouldn't it work as it's own Module???

 

[Gruuuu]

Awesome, you got it before I submitted.

It always feels better when you figure it out, yeah?

 

[Andrzejek]

So it should also work like this:

[tt]
If Sheets("Pg1").Amended.Value Then
MyStr = MyStr & "Y"
Else
MyStr = MyStr & "N"
End If[/tt]

PS. My example was from UserForm with a checkbox and a command button just to show that it should work OK there. I didn't do any controls on the WorkSheet


Have fun.

---- Andy

 

[hext2003]

I've been working on this for hours! I thought this should be easy but was getting more frustrated the more things I tried.

I should have come here sooner. Thx for the help everyone!