Problems making userform textbox visible/invisible

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
Hi there,

I have a textbox (TBPsychology8) which is populated using the code below. I also want this box to be visible or invisible (depending on certain criteria) when you click a command button (CBDisc8).
The criteria are:
-If TBPsychology8 is blank and invisible then run the code to populate it and make the textbox visible.
- If TBPsychology8 already has text in it then all i want for it is to be made visible.
- If TBPsychology8 is already visible then make it invisible.

the code I have so far will show the textbox, populate it and when i click the command button a second time it will close it however it will not open the textbox again after this.

Thanks in advance for your help,

Mike

VBA Code:
Private Sub CBPsychology8_Click()


If TBPsychology8.Visible = True Then
TBPsychology8.Visible = False
Exit Sub
End If

If TBPsychology8.Value <> "" And TBPsychology.Visible = False Then
TBPsychology8.Visible = True
Exit Sub

ElseIf TBPsychology8.Value = "" And TBPsychology8.Visible = False Then

TBPsychology8.Visible = True

Dim R As Worksheet
Dim myvalue3 As String
Dim T As Range
        

Set R = Worksheets("Interpretations")
    myvalue3 = TextBox9.Value & " " & "Psychologically"
    Set T = R.Range("A:MM").Find(myvalue3, LookIn:=xlValues, lookat:=xlWhole)
    If Not T Is Nothing Then
       TBPsychology8.Value = T.Offset(0, 1).Value
       Else
Set R = Worksheets("Interpretations")
    myvalue3 = Comboxmajor9.Value & " " & "Psychologically"
    Set T = R.Range("A:MM").Find(myvalue3, LookIn:=xlValues, lookat:=xlWhole)
    If Not T Is Nothing Then
       TBPsychology8.Value = T.Offset(0, 1).Value

End If
End If
End If

End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,480
Office Version
  1. 2019
Platform
  1. Windows
Hi,
untested by try using the controls Change Event to do what you want

VBA Code:
Private Sub TBPsychology8_Change()
    With Me.TBPsychology8
        .Visible = CBool(Len(.Text) > 0)
    End With
End Sub

If you are populating the textbox with code, then I suspect that you may be able to dispense with following code in your CBPsychology8_Click code


VBA Code:
If TBPsychology8.Visible = True Then
TBPsychology8.Visible = False
Exit Sub
End If

If TBPsychology8.Value <> "" And TBPsychology.Visible = False Then
TBPsychology8.Visible = True
Exit Sub

ElseIf TBPsychology8.Value = "" And TBPsychology8.Visible = False Then

TBPsychology8.Visible = True

Dave
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
Hi Dave,

Thanks very much for your help.
Moving the code to a change event in another textbox did the trick, just put an IF statement before hand to run the code if the textbox was blank.

Thanks very much for your help!!
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,480
Office Version
  1. 2019
Platform
  1. Windows
most welcome - glad suggestion helped

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,127,404
Messages
5,624,543
Members
416,034
Latest member
Shiv kumar

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