Simplifying code in a Function Module.

  • Thread starter Thread starter Bobbak
  • Start date Start date
B

Bobbak

Hello All,
I was wondering if it is possible to do this; I have a form that has
number of text boxes that when a button is clicked it turns into combo
boxes, simply by toggling the visibility to the box. For example 3
textboxes, txtA, txtB, and txtC, and 3 combo boxes, cmbA, cmbB, and
cmbC. Now when the form is open all three of the text boxes are
visible, and the combo boxes are invisible, until a button is pressed,
and the text boxes become invisible, and the combo boxes become
visible.

As it stand the cold for the button goes like this:

Private Sub Toggle_Click()

If Forms!Form1.txtA.visible = True Then
Forms!Form1.txtA.visible = False
Forms!Form1.cmbA.visible = True
Else
Forms!Form1.txtA.visible = True
Forms!Form1.cmbA.visible = False
End If


If Forms!Form1.txtB.visible = True Then
Forms!Form1.txtB.visible = False
Forms!Form1.cmbB.visible = True
Else
Forms!Form1.txtB.visible = True
Forms!Form1.cmbB.visible = False
End If


If Forms!Form1.txtC.visible = True Then
Forms!Form1.txtC.visible = False
Forms!Form1.cmbC.visible = True
Else
Forms!Form1.txtC.visible = True
Forms!Form1.cmbC.visible = False
End If

End Sub

Now my question is, if there is a way to simplify this code using a
Function Module, and passing the variable names as the parameters.
 
I wouldn't find placing this in a module to be a lot easier unless you have a lot of these
buttons so that the code is being repeated a number of times. Basically, whatever is
easiest to type in and maintain. If you need to make changes, it is sometimes easier to
change things if it is all in one location. This assumes that the changes will always be
applied to all the connecting objects.

What you may find simpler (a little less typing) is replacing the Form call with Me if the
code is running on the same form it is referring to. If not, you could shorten up your
typing by creating a variable to hold part of the name.

Example:
Me.txtA.Visible
or
Dim frm As Form
Set frm = Forms!Form1
frm.txtA.Visible
......
Set frm = Nothing

Another thing that may shorten this is:

With Me
.txtA.Visible = Not .txtA.Visible
.cmbA.Visible = Not .txtA.Visible
.txtB.Visible = Not .txtB.Visible
.cmbB.Visible = Not .txtB.Visible
.txtC.Visible = Not .txtC.Visible
.cmbC.Visible = Not .cmbC.Visible
End With

If you went with the frm variable, you would replace Me in the With statement with frm.

If you had many more of these, I would be tempted to replace A, B, and C with 1, 2, and 3
and so on. You could then run a loop to toggle them.

Example:
With Me
For i = 1 To 3
.Controls("txt" & i).Visible = Not .Controls("txt" & i).Visible
.Controls("cmb" & i).Visible = Not .Controls("txt" & i).Visible
Next i
End With
 
Bobbak said:
Hello All,
I was wondering if it is possible to do this; I have a form that has
number of text boxes that when a button is clicked it turns into combo
boxes, simply by toggling the visibility to the box. For example 3
textboxes, txtA, txtB, and txtC, and 3 combo boxes, cmbA, cmbB, and
cmbC. Now when the form is open all three of the text boxes are
visible, and the combo boxes are invisible, until a button is pressed,
and the text boxes become invisible, and the combo boxes become
visible.

As it stand the cold for the button goes like this:

Private Sub Toggle_Click()

If Forms!Form1.txtA.visible = True Then
Forms!Form1.txtA.visible = False
Forms!Form1.cmbA.visible = True
Else
Forms!Form1.txtA.visible = True
Forms!Form1.cmbA.visible = False
End If


If Forms!Form1.txtB.visible = True Then
Forms!Form1.txtB.visible = False
Forms!Form1.cmbB.visible = True
Else
Forms!Form1.txtB.visible = True
Forms!Form1.cmbB.visible = False
End If


If Forms!Form1.txtC.visible = True Then
Forms!Form1.txtC.visible = False
Forms!Form1.cmbC.visible = True
Else
Forms!Form1.txtC.visible = True
Forms!Form1.cmbC.visible = False
End If

End Sub

Now my question is, if there is a way to simplify this code using a
Function Module, and passing the variable names as the parameters.
Click to expand...

Sure you could use a sub procedure to do this, but it may
not buy you much if you'd just rewrite the code. I believe
this will do the same as the code you posted:

Me.txtA.Visible = Not Me.txtA.Visible
Me.cmbA.visible = Not Me.txtA.Visible
Me.txtB.Visible = Not Me.txtB.Visible
Me.cmbB.visible = Not Me.txtB.Visible
Me.txtC.Visible = Not Me.txtC.Visible
Me.cmbC.visible = Not Me.txtC.Visible
 
You can certainly simplify the code, though I can't see any advantages to
moving it to a standard code module. In the form module you can use the Me
keyword to refer to the current form. Something like:

Dim blnComboVisible as Boolean

With Me
blnComboVisible = .txtA.Visible
.txtA.Visible = Not blnComboVisible
.txtB.Visible = Not blnComboVisible
.txtC.Visible = Not blnComboVisible
.cmbA.Visible = blnComboVisible
.cmbB.Visible = blnComboVisible
.cmbC.Visible = blnComboVisible
End with

If you renamed your controls txt1, txt2 etc then you could shorten the code
a bit more:

Dim blnComboVisible as Boolean
Dim i as Integer

blnComboVisible = Me.txtA.Visible
For i = 1 to 3
Me("txt" & i).Visible = Not blnComboVisible
Me("cmb" & i).Visible = blnComboVisible
Next
 
Back
Top