Dynamically Hide VBA elements based on Variable

SMit07

New Member
Joined
Feb 23, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello smart people,

I'm trying to develop a Userform which solves engineering problems. Long story short, the user selects how many nodes their structure has (for arguments sake 1-10) using a textbook and that value is stored under the variable "NumberofNodes". On the next tab of the Userform I have a list of abels from Nodes 1 down to Node 10 with corresponding x and y coordinate text boxes next to the labels. I'm trying to figure out how to hide the labels and text boxes based on the NumberofNodes value.

For example, if the user selects 4 nodes, I'd like the Label and coordinate boxes for Nodes 5, 6, 7, 8, 9 and 10 to be hidden upon hitting an "update button". I've attached some screenshots for ease of understanding.
Screen Shot 2020-04-06 at 10.39.18 pm.png
Screen Shot 2020-04-06 at 10.39.47 pm.png


Im thinking something along the lines of, once the update button is pressed, this code runs
VBA Code:
Private Sub Update_Button_1_Click()

NumberofNodes = NumberofNodes_Box.Value
counter1 = 1

For counter1 = 1 To NumberofNodes
    'eg first iteration will be Node_Label_1.visible = true
    Node_Label_"&"counter1.Visible = True
    'eg first iteration will be X1.visible = true
    ''' This correlates to the X coordinate inputbox
    X"&"counter1.Visible = True
    'eg first iteration will be Y1.visible = true
    ''' This correlates to the Y coordinate inputbox
    Y"&"counter1.Visible = True
Next counter1

counter1 = NumberofNodes + 1

For counter1 = NumberofNodes + 1 To 10
    Node_Label_"&"counter1.Visible = False
    X"&"counter1.Visible = False
    Y"&"counter1.Visible = False
Next counter1


End Sub

I know the syntax is clearly wrong, I'm not even sure if you can do this to begin with but any help would be greatly appreciated!!!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
From what I can understand, try
VBA Code:
Private Sub Update_Button_1_Click()
'
NumberofNodes = CLng("0" & NumberofNodes_Box.Value)
'
For counter1 = 1 To 10            '<<< Available rows
    Me.Controls("Node_Label_" & counter1).Visible = (counter1 <= NumberofNodes)
    Me.Controls("X" & counter1).Visible = (counter1 <= NumberofNodes)
    Me.Controls("Y" & counter1).Visible = (counter1 <= NumberofNodes)
Next counter1
End Sub
Or you should only "disable" those rows not to be used; in this case, replace .Visible with .Enabled
Bye
 
Upvote 0
From what I can understand, try
VBA Code:
Private Sub Update_Button_1_Click()
'
NumberofNodes = CLng("0" & NumberofNodes_Box.Value)
'
For counter1 = 1 To 10            '<<< Available rows
    Me.Controls("Node_Label_" & counter1).Visible = (counter1 <= NumberofNodes)
    Me.Controls("X" & counter1).Visible = (counter1 <= NumberofNodes)
    Me.Controls("Y" & counter1).Visible = (counter1 <= NumberofNodes)
Next counter1
End Sub
Or you should only "disable" those rows not to be used; in this case, replace .Visible with .Enabled
Bye


Thanks Anthony, it worked a treat.
I've just had another thought, instead of hiding and showing the labels and boxes every time a new value is entered, would there be a way to:
- Delete all Node labels and Node coordinate boxes except for the first one
- Copy the node label and coordinate boxes down for x amount of times correlating to the user input "Number of Nodes".
- Rename said copied labels and boxes to match a certain naming convention

I know this could be a big task however for what I'm solving, there could be anywhere from 1 to 99999999 nodes and manually making all of those on the VBA editor sounds like something out of hell.

(I'm still only learning this sort of stuff on VBA and really do appreciate all your help)
 
Upvote 0
You can programmatically add controls to a userform, but I think you will have some problems in managing the height of a userform with 20 rows of controls; my suggestion is that you switch to a structure that is easier to handle, for example ...a Table where the user can insert 1 to 1 million rows of data

Bye
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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