For... Next

[Sylvian]

Hello,

Does anybody of you know, how I can make the following "For… next"– string?

This is how it is currently working:

val = Worksheet1.Range("h13").Value
If Worksheet1.Range("h13").Value = "x" Then
Worksheet1.CheckBox6.Value = True
Else
Worksheet1.CheckBox6.Value = False
End If
val = Worksheet1.Range("h14").Value
If Worksheet1.Range("h14").Value = "x" Then
Worksheet1.CheckBox7.Value = True
Else
Worksheet1.CheckBox7.Value = False
End If

This is I want to make it:

Dim iCounter As Integer

For iCounter = 8 To 38

val = Worksheet1.Cells(iCounter,1).Value
If Worksheet1. Cells(iCounter,1).Value = "x" Then
Worksheet1.CheckBox6(iCounter,1).Value = True
Else
Worksheet1.CheckBox6(iCounter,1).Value = False
End If

[Dave Peterson]

You can assign a linked cell to each of your checkboxes, so that if you put True
or False into the cell the associated checkbox will change (checked or not
checked).

But if you want, you could use some code like this:

Option Explicit
Sub testme01()

Dim iCounter As Long
Dim wks As Worksheet
Dim Val As Variant

Set wks = Worksheets("sheet1")

For iCounter = 1 To 3
Val = wks.Cells(iCounter, 1).Value
If LCase(Val) = "x" Then
wks.OLEObjects("CheckBox" & iCounter).Object.Value = True
Else
wks.OLEObjects("checkbox" & iCounter).Object.Value = False
End If
Next iCounter

End Sub

or more simply:

Option Explicit
Sub testme01()

Dim iCounter As Long
Dim wks As Worksheet
Dim Val As Variant

Set wks = Worksheets("sheet1")

For iCounter = 1 To 3
Val = wks.Cells(iCounter, 1).Value
wks.OLEObjects("CheckBox" & iCounter).Object.Value = CBool(LCase(Val) = "x")
Next iCounter

End Sub

But I'm confused on where the cells are and what the associated names are for
each checkbox. You used H13, but then asked about cells(icounter,1) (that 1
means column A).

(I only tested with 3 checkboxes.)

Sylvian wrote:
>
> Hello,
>
> Does anybody of you know, how I can make the following "For… next"–
> string?
>
> This is how it is currently working:
>
> val = Worksheet1.Range("h13").Value
> If Worksheet1.Range("h13").Value = "x" Then
> Worksheet1.CheckBox6.Value = True
> Else
> Worksheet1.CheckBox6.Value = False
> End If
> val = Worksheet1.Range("h14").Value
> If Worksheet1.Range("h14").Value = "x" Then
> Worksheet1.CheckBox7.Value = True
> Else
> Worksheet1.CheckBox7.Value = False
> End If
>
> This is I want to make it:
>
> Dim iCounter As Integer
>
> For iCounter = 8 To 38
>
> val = Worksheet1.Cells(iCounter,1).Value
> If Worksheet1. Cells(iCounter,1).Value = "x" Then
> Worksheet1.CheckBox6(iCounter,1).Value = True
> Else
> Worksheet1.CheckBox6(iCounter,1).Value = False
> End If
>
> --
> Sylvian
> ------------------------------------------------------------------------
> Sylvian's Profile: http://www.excelforum.com/member.php...o&userid=26730
> View this thread: http://www.excelforum.com/showthread...hreadid=401819

--

Dave Peterson

[Sylvian]

Hi Dave,

Thanks for your Help, I'll try this out.

Best Regards,

Sylvian