make all empty textboxes invisible

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi all,


    I have a form with a number of textboxes on it.


    After they have been populated, any of them that are blank need to be invisible.


    The next textbox also needs to be invisible,


    The code i have that works fine is,


    Code
    If ws.Range("a4").Value = "" ThenMe.TextBox5.Visible = False
Me.TextBox6.Visible = False
Else
Me.TextBox5.Visible = True
Me.TextBox6.Visible = True
Me.TextBox5.Value = ws.Range("a4").Value
Me.TextBox6.Value = ws.Range("b4").Value
End If
'////////////////////


    This works fine but as there are 90 or so textboxes on this form i don't want to have to type this out loads of times.


    Is there a loop that i can use which will achieve the same thing.


    Thanks in advance

    Re: make all empty textboxes invisible


    Without knowing the relationship (For example - are all the textboxes using the default sequential names and are in order) between the textboxes there's no way to suggest how you could loop.


    What you could possibly do (again, not really enough info to be sure) is populate the textboxes then loop though all controls setting those textboxes that are blank to be hidden.


    Code
    Dim c as Control


For Each c in Me.Controls
    If typeName(c) = "TextBox" Then
        c.Visible = (c.Text <> vbNullstring)
    End If
Next


    If there are other textboxes on the userform you do not want to be affected by this then you will have to add another check. Perhaps add a '*' to the tag property of those and check the Tag in the loop.

    Re: make all empty textboxes invisible


    This actually works very well and was what i was looking for.


    Thanks again cytop.


    Also i had a look at that link you sent me about GIT and it is useful but i am after step by step instruction telling me how to start and get my work onto git.


    Thanks


    Adam

    Re: make all empty textboxes invisible


    It's not easy - made more complicated by the fact the VBA source is contained in workbooks. Unless there is a compelling reason for you to use GITHub (other than you want to use it) it might be easier just to keep backup copies of workbooks until you feel comfortable with the code needed for Git.


    Note also that it's not 'automatic' updates - you need to run code in your workbook to get/update source files and for that you need to enable access to the VBA object model, so there are security implications are well.The last line on that page I linked says:


    "Finally, Don't forget to delete the bootstrap module and turn off access to the VBA object model"

    Re: make all empty textboxes invisible


    OK thanks cytop. I think i will leave it for now. What is was excited about was the theory that it would record any changes that have been made. In the future some staff will be allowed to change some cells on a sheet and i would like to know exactly what they have changed from/to.

    Re: make all empty textboxes invisible


    Start a new thread, explain what you want to audit (Sheet/Sheets/Workbook).


    I have exisitng code to track all changes to a worksheet (or whatever) to an external text file. It can only record changes to cell values, not deleting/inserting rows/columns but for basic auditing it's easy and more importantly, fast.


    Records the Date, Time, Machine Name, UserName, Workbook name (it's used in more than 1 workbook, probably wil remove that), Worksheet name, cell address, Old Value, New Value


    hi


    how can i replicate this code for check boxes
    if the text box next to the check box is empty hide the check box ?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login