Select Case not filling in cells

  • Rookie VBA user. I am trying to create a user form that will fill in a variety of cells based on the value chosen from the combo box. When I run the form. it allows me to select the employee and if I click submit. it closes fine but none of my data carries over. "Front Sheet" is the sheet where I need the data inputted. LookupList is the sheet where I am getting the data from. I used Sheet1.Range and nothing happens.

    Edited once, last by pike ().

  • Welcome to the forum!

    I don't know if B4 is in ws or the ActiveSheet.

    Rather than sheet codename for output, I used:

  • You have many issues with your code.

    First off, you would be well served if you used Option Explicit as first line of code in objects like userforms, modules, and such. This will force you to Dim the variables in each routine if not global. e.g. You set the worksheet object as ws1 in one routine but did not in the other routine but used it as if it existed. I set this line automatically when I add VBE objects in my VBE's Tools > Options > Require Variable Declaration.

    Another issue is your use of Column. Since you only have one column in the combobox, Column use is not helpful.

    You should decide how you want to do the lookup. (1) You can go by the value selected in the combobox and use formula lookups to get the column values, or (2) fully fill the combobox so that you can use the combobox's Column method. (2) would be my recommendation unless your lookup range is huge.

    Always code with structure. This means indent your code for the loops and such. This makes code easier to understand and modify later. I use two space character widths when I press Tab key for my indents so it may not be that obvious in pasted code here. The default in the options is 4 space character width tabs.

    Prior to a Run, Compile (Debug menu) your project. This may catch some syntax errors.

    Once you decide on the lookup method, post back and I will show you how to do it.

  • If you went with method 2:

  • Alright so Option Explict is a must with userforms. From what you're saying is this is a fool proof command that will limit my coding mistakes correct? I did it under the options. It will always add it automatically or will I always have to declare my userforms to be Option Explicit?

    I think both of your recommendation are feasible. However in this case the look up in my case spans to 10 columns. My intention is to have the userform only display the employee name and not the rest of the information.

    in line six of the code you posted. you did Resize (,6). Value. That is what shows the row up to 6 columns correct? So if i did 0 it would only show the A column.

    and Case Else is another one I forgot. Is it good practice to always have a Case else anytime I am going to have a Select Case, Case statement?

    I will also make the changes and add some to see what you think and then post said changes or additions.

  • Alright so i added another statement to add the employee ID from a different column based on the selection made from the combo box. I changed the column count = 1. I don't want all of the other information displayed in the combo box. All I have to do now is add the rest of the Case variables. I am also going to change the combo box to make it searchable. but I think we got it all figured out.

  • alright so i ran the debug and I am getting "could not get the column property. invalid property array index" error and it highlights line 19 from my post. I think this what you were talking about when using the column method. it only happens when i delete the selection made from the combo box

  • You can use find instead of using case

  • So I still needed the Case for the reason of needing to change the value of column E. Check it out and let me know what you guys think. However your code for the combo box got rid of my column errors I was having after changing the code. Here is the final code put together. It works really well. Exactly what I wanted. thanks to both of you that helped me on this project. and i learned fairly quick that its .Cells not just cells lol. Excited to get started on my next project. Learned so much just getting in there and doing it over and over.

  • This would be a bit shorter, the data is in a hidden sheet

    The you just edit the worksheet not the code.

  • Line 21 gives a debug error if anything from the combo box is misspelled. Happens to me on the code I posted as well. I was under the impression the combo box was searchable. Now I have to code it to loop?