VBA code and "Form" to collect 1 to 4 variables

D

Dennis

Office 2003 Pro

Need help with setting up a query box where one could chose up to 4 variables.

I none are checked the user would be notified that there were no selections
and would exit the routine.

Something like this:

O Soft Return
O Hard Return
O Line Feed
O Three spaces with two

Do I need to set up a form?

Is there a way to do it all in VBA code? Do I need the form if I use VBA?

How do I incorporate the VBA with the Form?

Dennis
 
J

Jean-Guy Marcil

Dennis was telling us:
Dennis nous racontait que :
Office 2003 Pro

Need help with setting up a query box where one could chose up to 4
variables.

I none are checked the user would be notified that there were no
selections and would exit the routine.

Something like this:

O Soft Return
O Hard Return
O Line Feed
O Three spaces with two

Do I need to set up a form?

Is there a way to do it all in VBA code? Do I need the form if I use
VBA?

How do I incorporate the VBA with the Form?

I think you have to be a bit more forthcoming with details here.

What kind of form (on-line form or userform) are you talking about?
How are
O Soft Return
O Hard Return
O Line Feed
O Three spaces with two
variables?
What you are going to do with the user choices?
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
D

Dennis

Lean-Guy,

What I wish is to activate a Macro that starts a dialog box that asks the
user to make a choice of one or all of the following group of four
find/replace:

Sub FindReplaceHardSoftReturn()
With Selection.Find
.Text = vbLf 'Line Feed Chr(10)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With

With Selection.Find
.Text = "^l" 'Soft Return Chr(11)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With

With Selection.Find
.Text = "^p" 'Hard Return Chr(13)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With

With Selection.Find
.Text = " " 'Three spaces replace with two
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With

End Sub

I do not know how to setup and "call" a userform in VBA. Can you help?

Dennis
 
J

Jean-Guy Marcil

Dennis was telling us:
Dennis nous racontait que :
Lean-Guy,

What I wish is to activate a Macro that starts a dialog box that asks
the user to make a choice of one or all of the following group of four
find/replace:

Sub FindReplaceHardSoftReturn()
With Selection.Find
.Text = vbLf 'Line Feed Chr(10)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With

With Selection.Find
.Text = "^l" 'Soft Return Chr(11)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With

With Selection.Find
.Text = "^p" 'Hard Return Chr(13)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With

With Selection.Find
.Text = " " 'Three spaces replace with two
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With

End Sub

I do not know how to setup and "call" a userform in VBA. Can you
help?

For a start, see the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Once you have the userform, write some code in the OK button On_Click event
that will check the value of the checkboxes (use checkboxes, not radio
buttons because radio button are meant to be exclusive) and then fire the
appropriate code accordingly.
Come back with specific questions if you have any once you have something up
and running...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
D

Dennis

Jean-Guy,

I have a UserForm1.

The sub-routines are empty:

Private Sub HardReturn_Click()

End Sub

Private Sub LineFeed_Click()

End Sub

Private Sub SoftReturn_Click()

End Sub

Private Sub ThreeSpaces_Click()

End Sub

I am not sure how to do the following in VBA:
1) to check the check-box status of the four checkboxes on the UserForm
2) how to assign a value?; to what type of variable?
3) pass the four variables to the four subroutines that follow
3) structure the code for the userform

If you have some sample code, I may be able to adapt it. Thanks

Dennis
 
J

Jean-Guy Marcil

Dennis was telling us:
Dennis nous racontait que :
Jean-Guy,

I have a UserForm1.

The sub-routines are empty:

Private Sub HardReturn_Click()

End Sub

Private Sub LineFeed_Click()

End Sub

Private Sub SoftReturn_Click()

End Sub

Private Sub ThreeSpaces_Click()

End Sub

I am not sure how to do the following in VBA:
1) to check the check-box status of the four checkboxes on the
UserForm 2) how to assign a value?; to what type of variable?
3) pass the four variables to the four subroutines that follow
3) structure the code for the userform

For the OK button, in the Click event, have code similar to this:

With Me
If .HardReturn.Value = True Then 'Call the appropriate macro
If .LineFeed.Value = True Then 'Call the appropriate macro
If .SoftReturn.Value = True Then 'Call the appropriate macro
If .ThreeSpaces.Value = True Then 'Call the appropriate macro
End With

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
D

Dennis

Jean-Guy,

This is what I came up with. It works but not the way I wish.

Each check causes an instant action where I wanted the action to occur only
after I click OK.

Obviously I did something wrong.

Thoughts?

BTW Thanks so much for your time and knowledge!

Dennis

Private Sub HardReturn_Click()
With Selection.Find
.Text = "^p" 'Hard Return Chr(13)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub
Private Sub SoftReturn_Click()
With Selection.Find
.Text = "^l" 'Soft Return Chr(11)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub
Private Sub LineFeed_Click()
With Selection.Find
.Text = "^l" 'Soft Return Chr(11)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub
Private Sub ThreeSpaces_Click()
With Selection.Find
.Text = " " 'Three spaces replace with two
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub
Private Sub OKButton_Click()
With Me
If .HardReturn.Value = True Then Call HardReturn_Click
If .LineFeed.Value = True Then Call LineFeed_Click
If .SoftReturn.Value = True Then Call SoftReturn_Click
If .ThreeSpaces.Value = True Then Call ThreeSpaces_Click
End With
Unload UserForm1
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
 
J

Jean-Guy Marcil

Dennis was telling us:
Dennis nous racontait que :
Jean-Guy,

This is what I came up with. It works but not the way I wish.

Each check causes an instant action where I wanted the action to
occur only after I click OK.

Obviously I did something wrong.

As written, you execute the code twice, when the user clicks on the checkbox
and when the OK button is clicked.

You do not need any code for the checkboxes. Just put the find/replace code
in a regular module, let's say FindReplaceMacros, then use something like:

Private Sub OKButton_Click()

With Me
If .HardReturn.Value = True Then FindReplaceMacros.Call HardReturn
If .LineFeed.Value = True Then FindReplaceMacros.Call LineFeed
If .SoftReturn.Value = True Then FindReplaceMacros.Call SoftReturn
If .ThreeSpaces.Value = True Then _Click.Call ThreeSpaces
End With

Unload UserForm1

End Sub
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
D

Dennis

Jean-Guy,

Never would I have thought that the code would have ended up so easy.

You taught me how to fish - not just giving me the fish.

Thank you very much.

One last thing. the general code routines use "With.Selection Find"
yet each sub-routine processes the entire file - not just what I
selected prior to running the macro.

What would I do to limit the processing to mySelection?

Dennis

The "final" code is as follows:

General Module: (Named: FindReplaceMacros)

Sub FindReplaceHardSoftReturn()
FindReplaceForm.Show
End Sub

Sub HardReturn()
With Selection.Find
.Text = "^p" 'Hard Return Chr(13)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub

Sub SoftReturn()
With Selection.Find
.Text = "^l" 'Soft Return Chr(11)
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub

Sub LineFeed()
With Selection.Find
.Text = "^l" 'Soft Return Chr(11)
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub

Sub ThreeSpaces()
With Selection.Find
.Text = " " 'Three spaces replace with two
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub
*******************************FindReplaceForm
Code***************************************

Private Sub OKButton_Click()

With Me
If .HardReturn.Value = True Then FindReplaceMacros.HardReturn
If .LineFeed.Value = True Then FindReplaceMacros.LineFeed
If .SoftReturn.Value = True Then FindReplaceMacros.SoftReturn
If .ThreeSpaces.Value = True Then FindReplaceMacros.ThreeSpaces
End With
Unload FindReplaceForm
MsgBox "Process Completed! Press OK to Continue"

End Sub

Private Sub CancelButton_Click()
Unload FindReplaceForm
End Sub
 
J

Jean-Guy Marcil

Dennis was telling us:
Dennis nous racontait que :
Jean-Guy,

Never would I have thought that the code would have ended up so easy.

You taught me how to fish - not just giving me the fish.

Thank you very much.

One last thing. the general code routines use "With.Selection Find"
yet each sub-routine processes the entire file - not just what I
selected prior to running the macro.

Have you tried replacing

.Wrap = wdFindContinue

with

..Wrap = wdFindStop

?
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
D

Dennis

Jean,

I had not ...
Then I did ...
It worked!!

The MVP's are an incredible benefit to all.

Thanks again Dennis
 
J

Jean-Guy Marcil

Dennis was telling us:
Dennis nous racontait que :
Jean,

I had not ...
Then I did ...
It worked!!

The MVP's are an incredible benefit to all.

Just glad I could help!

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top