In the previous Screencast, I showed you how we can create this Add Record form. And we can add a name, an address, and a phone number for that new person. And it'll be placed onto the spreadsheet as a new row. You ultimately, want your project, your ultimate organizer, to be able to detect the number of columns that you have information in. And we're going to assume that we can go up to 12 columns. And for each of those column labels, name, address, phone number, and so on, we're going to going to add this area on the Add Record form that the user can input the new data, the new information. So down here, I've actually got, in the bottom here, two more hidden labels and text boxes, or input boxes. And let me show you what happens when we add in a new column. So if I add in a new column, like maybe Email. And let's do a fifth column, Shoe Size. And now, when I do Add Record, it's going to count, it's going to detect the number of columns. And now, what it's done is it has unhidden those two extra labels here and the two extra input boxes. So whenever you add a record, it's going to automatically detect all of the columns that exist on the spreadsheet. And it's going to make available input fields for those different categories. So that's what I'm going to show you how to make in this Screencast. I've got this in a starter file called AddRecord_Unhide_Fields- STARTER.xlsm. It basically starts with what we left off on the previous Screencast. So this is essentially what we left off with with the previous Screencast. But now, what I've done is I've added a couple more of these. So I've added label four, label five, input four, and input five. So we're going to make this adaptable and detect the number of columns that we have. For example, if I only have two columns of information here, then I'm going to only unhide or reveal two of these pairs of labels and input boxes. We're going to have to make a couple of modifications to the PrepareForm sub. We don't know the size, to begin with. So I'm going to go ahead and delete that three. We're going to dim this as unknown size. We're going to count the number of rows. But we also have to count the number of columns. So we can use WorksheetFunction.CountA, and that'll be the number of columns. So in this example, that'll be one, two, three. So if I had more then we would automatically detect that. We're going to select Range A1. And now, instead of iterating for i = 1 To 3, we're going to iterate to number of columns. We're still going to take in categories vector. The one thing I do have to do before we do that is we have to ReDim our categories vector. So we're going to iterate through for i = 1 To number of columns. Now, instead of just setting each individual label to the categories, we don't know, to begin with, how many categories we're going to have. And we want this thing to be dynamic and to always test and check for the number of columns. So I'm going to go ahead and modify this. The first thing I'm going to do is just with UserForm1, Input1.Visible = False. So all of these input fields, Input1, 2, 3, 4, 5 are all going to be false. What that means is these are all going to be nonvisible, they're going to be hidden. The labels by default are already hidden because they don't have anything in them. So these just don't have any text in them, so they're hidden by default. Now, I'm going to add this If statement. If the number of columns is greater than or equal to 1 Then the Label1 is going to be equal to categories of 1. If I have two columns, then I can add a similar statement. If the number of columns is greater than or equal to 2 Then the second Label is going to equal categories of 2 that we picked up earlier. And we're also going to make, in each of those If statements, we're going to make those Input fields visible, if this is the case. So let's say I had three columns. The first If statement is going to be satisfied because 3 is greater than or equal to 1. So we're going to reveal Label1. We're going to set that equal to the first element of our categories vector. And then we're going to make it visible. 3 is greater than 2, so we're going to make the second Label equal to the second category. And we're going to reveal, or make that visible. And then for n columns equals 3, we have to do this one more time. It turns out that if we have five boxes, then we're going to have to do the same thing. So we're going to have to do that for if the number of columns is greater than or equal to 3, 4, and so on, up until the final one. Now, this last one, it doesn't really matter because you're probably not going to have the number of columns greater than 5. We're making that assumption, so we can just change that to an equal. For your ultimate organizer project, you're going to have up to 12. So what you're going to have to do is add in a lot more of these up until 12. So let's go ahead and run through this. I'm just going to put a break point maybe down here. And I'm going to start in my run form, I'm going to press F5 to run. Oops, I forgot to define number of columns as an integer. So now, let's press F5. Now, with UserForm1, I'm going to press F8, and we're going to hide all of the forms. And right now, remember, we haven't shown UserForm1 yet. We're still in this PrepareForm subroutine. So we're going to hide all of them. And then depending upon what the number of columns is, we determine number of columns to be three down here because we have three columns of information. So for this first one, this first if statement, that's true. So we're going to set Label1 equal to the first category, and that's Name. And we're going to reveal. So that statement we just revealed that input box. And then I'm going to go into the second one because we have three columns. We're going to do the same thing for the second and third. But the fourth and fifth, we don't have, so we're just going to bypass those. Those labels and input fields are going to remain hidden. And then we bump out of that and we show UserForm1. So we just showed those three different categories based upon the three headings up here on the spreadsheet. Now, if I add a couple other headings here, maybe Email and Shoe size, when I run this, you see that it auto detects those five columns here, and puts the labels here on the left. And we have our text boxes here. And then we can add, if we want, we can add new information, and a new record can be placed onto the spreadsheet. I wanted to show you one more thing, and that is how you can change the size of this box, because you're going to want to accommodate up to 12 different category names. And so you might have a sixth one down here at the bottom. But then if you wanted 12, this thing would be really big if you wanted to go down. So let me show you how we can customize this and change the width depending upon the number of categories that we have. So there is a property of a user form down here. So if I click on my UserForm, and I go down to the bottom here, we have Width. That's something that you can use or you can change. So right now the width is 260. I'm going to change this to 250. It's going to narrow it a tiny bit, but that's okay. So right now, the width is 250. Now, let me increase this to something maybe like 450. And I am going to change around these. I'm just going to take these and I'm going to drag them over here. Actually, let me increase the width of this to 500. It'll be a little bit bigger. And I'm going to drag these over like so. I'm also just going to shorten this. So we have two different options for the width. We have the current, which is 500, or I can change this back down to 250. So 250 is what we want when we have three or fewer categories on our spreadsheet. 500 is what we want when we have four or five different categories. This is going to go into the code before we open up the UserForm. It's going to go in the PrepareForm sub. If nc is greater than or equal to 1, then I'm going to say UserForm1.Width = 250. So I'm also going to put it here. And I'm going to put it here for this third one. But then, what I'm going to do is if we're greater than or equal to 4, or greater than or equal to 5, I'm going to change these both to 500, all right? So that's how we can easily change the width of this user form that pops up. So now, let's go through this. I'm just going to press F5 when I'm in the RunForm sub. And you see right now, we have five categories on the spreadsheet. And the total width is 500, all right? So that's working properly. But now, if I delete these two columns, and I run this again, then it detects that there's only three categories, and so the width is only 250. So this is how you can sort of customize the width of your UserForm depending upon the number of columns.
