Userform variables with bookmarks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LanWanMan - 24 Apr 2006 18:00 GMT

Hello,

Currently, I am using the following vba code to insert a checkmarked item at
a bookmark:

If UserForm1.CheckBox1.Value = True Then
With ActiveDocument
.Bookmarks("Exclusion1").Range _
.InsertBefore TextBox1
End With
End If

If UserForm1.CheckBox2.Value = True Then
With ActiveDocument
.Bookmarks("Exclusion2").Range _
.InsertBefore TextBox2
End With
End If

I would like to insert TextBox2 at "Exclusion1" bookmark if checkbox1 is not
checked, ie, I would like the first bookmark used even the first checkbox
checked is down the list. Can this be done with some type of for / next
loop? Essentially, the user needs to pick and choose which items to insert
with a checkbox and those selections needs to begin at the top of the
bookmarks.

Thanks,

Michael

Please reply to the newsgroup.

Reply to this Message

Doug Robbins - Word MVP - 24 Apr 2006 20:01 GMT

Use

If UserForm1.CheckBox1.Value = True Then
With ActiveDocument
.Bookmarks("Exclusion1").Range _
.InsertBefore TextBox1
End With
ElseIf UserForm1.CheckBox2.Value = True Then
With ActiveDocument
.Bookmarks("Exclusion1").Range _
.InsertBefore TextBox2
End With
End If

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Hello,
>
[quoted text clipped - 27 lines]
>
> Please reply to the newsgroup.

Reply to this Message

LanWanMan - 24 Apr 2006 23:09 GMT

Doug....thanks, but that is what I am using.

Actually the user might select one or all of up to 40 different "Exclusions"
to insert into the proposal.

Thanks,

Michael

> Use
>
[quoted text clipped - 41 lines]
>>
>> Please reply to the newsgroup.

Reply to this Message

Russ - 25 Apr 2006 07:01 GMT

This might help you loop through all the checkboxes in a UserForm if they
have the default names of CheckBox1,2,etc. Then iterate a counter variable
inside the loop to do things sequentially with your document bookmarks.

Private Sub CommandButton1_Click()
Dim Count As Integer
Dim Control As Control
For Each Control In UserForm1.Controls
If Control.Name Like "CheckBox*" Then
MsgBox Control.Value 'your code here i.e. If Control.Value then ...
End If
Next Control
End Sub

On 4/24/06 6:09 PM, in article emZ2Xw#ZGHA.504@TK2MSFTNGP03.phx.gbl,

> Doug....thanks, but that is what I am using.
>
[quoted text clipped - 50 lines]
>>>
>>> Please reply to the newsgroup.

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID <-- fix this before replying

Reply to this Message

LanWanMan - 27 Apr 2006 20:52 GMT

Russ,

Thanks for the help. I think I am on the right track, I can almost taste
it. Here is what I have so far:

Private Sub CommandButton1_Click()
Dim Count As Integer
Dim Control As Control
Count = 0
For Each Control In InclExcl.Controls
If Control.Name Like "Checkbox*" Then
Count = Count + 1
If Control.Value = True Then
With ActiveDocument
.Bookmarks("Exclusion" & Count).Range _
.InsertBefore ("TextBox" & Count)
End With
End If
End If
Next Control

Unfortunately, this does not error out, but also does nothing when executed.
I am learning my VB as I go and I understand programming. I am unable to
debug this as I do not know everything that is going on here.

Any help is appreciated.

Thanks,

Michael.

> This might help you loop through all the checkboxes in a UserForm if they
> have the default names of CheckBox1,2,etc. Then iterate a counter variable
[quoted text clipped - 69 lines]
>>>>
>>>> Please reply to the newsgroup.

Reply to this Message

Jean-Guy Marcil - 27 Apr 2006 21:44 GMT

LanWanMan was telling us:
LanWanMan nous racontait que :

> Russ,
>
[quoted text clipped - 22 lines]
> on here.
> Any help is appreciated.

Have you tried the code I posted:
(This version here is slightly different as the previous version did not do
the True test)

Dim myCounter As Long
Dim myControl As Control
myCounter = 0
For Each myControl In Me.Controls
If myControl.Name Like "CheckBox*" Then
If myControl.Value = True Then
myCounter = myCounter + 1
With ActiveDocument
.Bookmarks("Exclusion" & myCounter).Range _
.InsertBefore Me.Controls("text" & myControl.Name).Text
End With
End If
End If
Next

The counter should be inside the "If True" test, not before. You only want
to insert textboxes corresponding to true checkboxes. In your version, you
were counting all checkboxes, regardless of their True/False value.

See my post regarding a naming convention for the above to work properly.

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message

LanWanMan - 28 Apr 2006 19:25 GMT

Thank you for the help. Using your code as a template, I was able to make
it work well. The end user has a section of Exclusions and a section of
Inclusions that they select to insert into the document. I named the
Exclusion check boxes "ExcludeBox1" and the textbox was named
"textExcludeBox1". Likewise, I named the Inclusion check boxes
"IncludeBox1" and the textbox "textIncludeBox1". Here is the code I used to
make it work:

Dim myCounter As Long
Dim myControl As Control
myCounter = 0
For Each myControl In inclexcl.Controls
If myControl.Name Like "ExcludeBox*" Then
If myControl.Value = True Then
myCounter = myCounter + 1
With ActiveDocument
.Bookmarks("Exclusion" & myCounter).Range _
.InsertBefore inclexcl.Controls("text" &
myControl.Name).Text
End With
End If
End If
Next

myCounter = 0

For Each myControl In inclexcl.Controls
If myControl.Name Like "IncludeBox*" Then
If myControl.Value = True Then
myCounter = myCounter + 1
With ActiveDocument
.Bookmarks("Inclusion" & myCounter).Range _
.InsertBefore inclexcl.Controls("text" &
myControl.Name).Text
End With
End If
End If
Next

Again, thank you for your help.

> LanWanMan was telling us:
> LanWanMan nous racontait que :
[quoted text clipped - 50 lines]
>
> See my post regarding a naming convention for the above to work properly.

Reply to this Message

Jean-Guy Marcil - 24 Apr 2006 21:09 GMT

LanWanMan was telling us:
LanWanMan nous racontait que :

> Hello,
>
[quoted text clipped - 21 lines]
> choose which items to insert with a checkbox and those selections
> needs to begin at the top of the bookmarks.

Could the user select both checkboxes?
Are there only two checkboxes on the userform?

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message

LanWanMan - 24 Apr 2006 23:06 GMT

THanks for the reply...

Actually, there could be up to 40 items to check, I was just using the first
two to set example. The users wants to pick and choose which ones to
insert.

Thanks,

Michael

> LanWanMan was telling us:
> LanWanMan nous racontait que :
[quoted text clipped - 27 lines]
> Could the user select both checkboxes?
> Are there only two checkboxes on the userform?

Reply to this Message

Jean-Guy Marcil - 25 Apr 2006 14:26 GMT

LanWanMan was telling us:
LanWanMan nous racontait que :

> THanks for the reply...
>
> Actually, there could be up to 40 items to check, I was just using
> the first two to set example. The users wants to pick and choose
> which ones to insert.

With that many checkboxes, if the text to be inserted comes directly from
textboxes and does not need to be formatted, I would use one bookmark and
build a string to insert all the items in one go.

If this is not possible (there may be text between the bookmarks in the
document), then I would cycle each checkbox (you can use code like Russ')
and insert the text as needed.

To manage the bookmarks, I would name them all with a similar name
(Bookmark1, Bookmark2, Bookmark3, etc.) and just select the next available
bookmark using a the number in the name and a counter when cycling through
the checkboxes. Also, since there seems to be one checkbox for each textbox,
I would use a naming scheme such as:
CheckBox1 - textCheckBox1
CheckBox2 - textCheckBox2
CheckBox3 - textCheckBox3
etc.
Or any other scheme where the textbox name is the same as the checkbox,
except for a prefix or a suffix.

Using the code Russ suggested, you could get something like (there are other
ways, but let's stick to one way for now):

Dim myCounter As Long
Dim myControl As Control
myCounter = 0
For Each myControl In Me.Controls
If myControl.Name Like "CheckBox*" Then
myCounter = myCounter + 1
With ActiveDocument
.Bookmarks("Exclusion" & myCounter).Range _
.InsertBefore Me.Controls("text" & myControl.Name).Text
End With
End If
Next Control

(Untested... you may need to fiddle with it.)

Then, you could even add code for dealing with the unused bookmarks (you
would know how many are left because of myCounter).

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message

Rate this thread: