CODEWITHSUNDEEP
vbams-access
Bandito
Bandito

Reputation: 11

ms-access shorten vba code

I'm using the following code alot in my project: 

txtVoornaam.Locked = False
txtVoornaam.BorderStyle = 4
txtVoornaam.BorderColor = RGB(255, 165, 0

txtAchternaam.Locked = False
txtAchternaam.BorderStyle = 4
txtAchternaam.BorderColor = RGB(255, 165, 0)

txtAfdeling.Locked = False
txtAfdeling.BorderStyle = 4
txtAfdeling.BorderColor = RGB(255, 165, 0)

I wonder if there is a way to not display this in my code or shorten this. The code gets very long if i use this a few times..

Upvotes: 1

Views: 114

Answers (4)

Cody Geisler
Cody Geisler

Reputation: 8617

An option if you have several controls that you are creating through a form would be to do the following:

Dim names() As String
names = Split("txtVoornaam,txtAchternaam,txtAfdeling", ",")

Dim ctrl As Variant
Dim ctrlName As Variant

For Each ctrl In Me.Controls
  For Each ctrlName In names
    If StrComp(ctrlName, ctrl.Name) = 0 Then
        ctrl.Locked = False
        ctrl.BorderStyle = 4
        ctrl.BorderColor = RGB(255, 165, 0)
    End If
  Next ctrlName
Next ctrl

This code iterates through each of the control names that fit your list.

However, this is much less efficient than Mat's Mug's answer because you are iterating through the entire list of controls in your form, but it does showcase how you might take a list of static names and iterate through them and a collection.

If you wanted to change all the text controls this would be the way to do it; simply remove the ctrlName check.

As Parfait has correctly pointed out, you could shorten the code to the following if you are confident in your control names:

Dim names() As String
names = Split("txtVoornaam,txtAchternaam,txtAfdeling", ",")

Dim ctrlName As Variant

For Each ctrlName In names
  With Me.Controls(ctrlName)
      .Locked = False
      .BorderStyle = 4
      .BorderColor = RGB(255, 165, 0)
  End With
Next ctrlName

Upvotes: 2

O. Gungor
O. Gungor

Reputation: 768

if your controls are the same, obviously put them in a single sub/function that you can call from anywhere. i would not try to lock or change the format of textboxes, instead just enable/disable, and it will handle the format for you:

textbox.enabled = true/false

if you are doing this on multiple forms and really want a single sub/function to control enabling/disabling the textboxes on each form, then there are various ways of doing that as well, solution will depend on your needs, but certainly doable and some have already commented above.

for example, you can use the "tag" property of the textboxes to flag the textboxes on that form that you want to enable/disable. you can then have a single sub/function that would take in the form as reference, and then you can read the "tag" property of all textboxes on that form and if they are the ones you flagged, you would proceed to enable/disable them

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Whenever you need to repeat a set of instructions, instead of copy+pasta'ing code your first reaction should be to ask yourself "how can I avoid copying this chunk over and over?" - and the solution is pretty much always to extract a method and parameterize it.

So you take one of the repeated chunks:

txtAchternaam.Locked = False
txtAchternaam.BorderStyle = 4
txtAchternaam.BorderColor = RGB(255, 165, 0)

and then copy it one last time in a new scope:

Private Sub RenameMe()
    txtAchternaam.Locked = False
    txtAchternaam.BorderStyle = 4
    txtAchternaam.BorderColor = RGB(255, 165, 0)
End Sub

Then you extract the parameters:

Private Sub RenameMe(ByVal target As Control)
    target.Locked = False
    target.BorderStyle = 4
    target.BorderColor = RGB(255, 165, 0)
End Sub

Then you replace the repeated chunks with calls to that new procedure:

RenameMe txtVoornaam
RenameMe txtAchternaam
RenameMe txtAfdeling

Or if that's still tedious you can iterate controls and call that procedure in the loop body - whatever works best for you.

And if you need more flexibility, extract more parameters and make them Optional as needed:

Private Sub RenameMe(ByVal target As Control, Optional ByVal lockCtrl As Boolean = False, Optional ByVal brdrStyle As Long = 4, Optional ByVal brdrColor As Long = 42495)
    target.Locked = lockCtrl
    target.BorderStyle = brdrStyle
    target.BorderColor = brdrColor
End Sub

Now the hard part is to give RenameMe a meaningful name that properly conveys what's going on here. I'd suggest FormatControl or something along these lines.

Upvotes: 5

geeFlo
geeFlo

Reputation: 375

function Lockdown(strControl)

with me(strControl)
   .locked = false
   .borderstyle = 4
   .bordercolor = RGB(255,165,0)
end with

use me or forms!formname depending on where you're calling from

Upvotes: 1

Related Questions