Effects of Disabling A Frame

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have discovered that if I disable a frame in my userform, all the controls within that frame are also disabled.
I have a routine associated with a frame click event, so there are conditions when I don't want the user to be able to click the frame. In those cases, I have the frame disabled. When the conditions are met, the frame is enabled.
But the problem is, the controls within that frame are those in which the user has to change in order to get those conditions met. With those controls disabled as a result of their host frame being disabled, I am stuck not being able to enable the frame.

Is there a workaround to this?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
Hi,
Instead of disabling the Frame, you could use it's Tag property to hold a value you can test for & if met, exit the Click event sub

example
where in code you disable the Frame apply a value to the Tag property

VBA Code:
Me.Frame1.Tag = xlOff

and in your click event apply the test

VBA Code:
Private Sub Frame1_Click()
If Val(Me.Frame1.Tag) = xlOff Then Exit Sub

'rest of code
End Sub

and then where in your code you would re-enable the Frame just change the Tag value

VBA Code:
Me.Frame1.Tag = xlOn

Dave
 
Last edited:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Dave, thank you so much for sharing your idea. Unfortunately, I haven't been able to get it to work exactly the way you illustrated. My values for tag, despite setting them as xlon or xloff are not going into the frame1_click sub as such. When me.frame1.tag = xloff, the value that it carries into the frame1_click sub is -4146. When me.frame1.tag = xlon, the value is 1. So, regardless of the state that me.frame1.tag is, when it hits the frame1_click sub, tag never equals xloff so the sub never exits.

I've just substituted
Code:
If Val(Me.Frame1.Tag) = xlOff Then Exit Sub
With
Code:
If Val(Me.Frame1.Tag) = -4146 Then Exit Sub
to get it to work.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
Hi,
If the first line of your click event has the test line of code as shown, I can see no reason why should not work for you.
Can you share your forms codes that set the tag values

Dave
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Sure, see if this sheds any light ...

'Permit' is the userform, 'frm_cust' is the frame.

Code:
Private Sub frm_cust_Click()
    If Val(permit.frm_cust.Tag) = -4146 Then Exit Sub
    MsgBox "view extended customer info"
    Stop
End Sub
Of course this is my adaptation I needed to make irt work. Originally it was...
Code:
 If Val(permit.frm_cust.Tag) = xlOff Then Exit Sub

To "activate/enable" click event of the frame:
Code:
If cfc = 2 Then
        permit.frm_cust.Caption = "Customer   <Click to view extended info>"
        'permit.frm_cust.Enabled = True
        permit.frm_cust.Tag = xlOn
 End If

To "deactivate/disable" frame click event
Code:
permit.frm_cust.Tag = xlOff
Debug.Print permit.frm_cust.Tag
This debug statement returns -4146
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
Sorry not clear, is this now working for you?

-4146 is that value for constant xlOff - using the constant rather than it's value, is more descriptive of the intended action but its a choice for you.

Also as an aside, you can use the Me keyword in place of the userform name

Dave
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks Dave!
It is working if I use -4146 as the comparison. If I use xlOff in the comparison, it does not work.
In this project, I am unable to use Me for some reason. Everytime I try, I get an "Invalid use of Me keyword" error.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
Curious, you have some strange things going on in your project. Are you using Me keyword inside Userforms code page? or is code placed in standard module where you would get that error.

if Tag idea is now doing what you want - glad resolved

Dave
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Me being used in a standard module when I get the error.
Thanks to you things are workingbetter than they had been.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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
Top