How to pass variable in "Call Function" that can be used in me.variable.locked = true

PartsPig

New Member
Joined
Sep 13, 2024
Messages
30
Office Version
  1. 365
Platform
  1. Windows
I have a need to potentially preform the same actions on multiple fields of a form based on if it is clicked or not. I have written the code below in an attempt to minimize the amount of code and make it easier to follow. The problem is that when I try to compile it I get a Compile error: Method or data member not found. Is it possible to pass a field name through a "call function" that can be used as a data member? When I run the code in a single Sub in line with the rest of the code it works fine. Just didn't want to have to duplicate the code everywhere else it's needed in case I need to update or modify it sometime later.

VBA Code:
Private Sub Inv_Num_Click()
    myUnlock INV_Num
End Sub
.
.
.
Private Sub myUnlock(myField)
    Dim myResult As Boolean
    
    If Me.myField.Locked = True Then
        myResult = (MsgBox("Do you want to unlock the " & myField & " field?", vbYesNo, "Unlock field") = vbYes)
        If myResult = True Then
<more code>
End Sub
 
Micron,

I really like the idea of using the double click to trigger the unlock event. I have incorporated it throughout my DB form and found it also addresses unlocking checkboxes where the single click method did not. I was so happy to see that! Thanks for that suggestion!

I had already changed the msgbox as the popups once the field was unlocked were very annoying.

I left everything else as it is since it is working and I don't want to screw it up. LOL. I will however go through the proposed changes and likely incorporate them sometime soon. Please keep an eye out for future questions from PartsPig. I would love your input.
 
Last edited:
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Dave,

I am very much a proponent of the DRY method but have been out of the game so long I feel like a beginner again. I ended up changing the msgbox to do nothing if the field was already unlocked as the popups were very annoying, especially when having to correct multiple entries. Please keep an eye out for future questions from PartsPig. I would love your input.
 
Upvote 0
I'd say that you're not using Option Explicit; at least not with that sub. If you were you'd raise an error in the calling sub because the parameter isn't declared

I just updated & reposted OPs published code but I am fully aware of the point you make, all my modules have Option Explicit declared & in this case, code compiled ok for me

Dave
 
Upvote 0
Dave,

I am very much a proponent of the DRY method but have been out of the game so long I feel like a beginner again.
If you see code being repeated across a project that largely does the same thing, then always worth thinking about ways to reduce it.
I ended up changing the msgbox to do nothing if the field was already unlocked as the popups were very annoying, especially when having to correct multiple entries.
Too many message boxes can be annoying that's why in well developed applications, users are given option to surppress them.

Good luck with project

Dave
 
Upvote 0
Option to supress yo
Option to suppress you say, Hmmmmm. I like that but it will be for later during the "let's make it pretty" stage - LOL.
 
Upvote 0

Forum statistics

Threads
1,226,269
Messages
6,189,956
Members
453,584
Latest member
daihoctuxaeptit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top