VBA form for data entry

[anhlu]

Hi everyone, I need help building VBA. I have this spreadsheet:

Contract Rank Performance
A
B
C
D
E
F
G
H
I

Every month, we need to update the KPI Report for 9 of our contracts. I’m very new with VBA. So far, I’ve created the Data Entry form and 1 code.

Contract’s combobox’s code is:

Private Sub UserForm_Initialize()
Me.ComboBox1.List = Sheets(1).Range("A2:A10").Value
End Sub

Q1: Can someone show me how to change the range so that it doesn’t always start at A2 but under the title Contract and it doesn’t always end at A10 so that when we add more contracts the list will show up in the combobox?

Q2: I also want to rename sheet 1 to Data, how do I write this code as Sheets(Data) instead of Sheets(1)?

Q3: After choose the contract name on the form, can someone show me a code so that the Rank’s textbox on the form will link to the according cell on the spreadsheet?

Q4: The Performance column is set up with conditional formatting. Green dot is number 1, Yellow dot is number 2, Red dot is number 3. Can someone show me a code so that when I choose the OptionButton on the form, the right dot will show up on Performance?

Thanks!

[Keebellah]

Hi, looks good and you've got quite a lot of questions.
Renaming a sheet just rename it and make sure the code is correct
Double click the tab and change the text Sheet 1 to Data and do the same in your code Sheets("Data")
The combobox will always start at 2 but you can make it dynamic and conditional formatting is not recognized in vba bu the formula that decides the coloring yes,

I suggest you attach a sample file since I don't think you expect any of use to create a workbook for you and try and figure out the userform and all that.
Attach a file and any one of us can pick it up and help you adding comments in the vba code explaining what does what and when.
Include the explanation of what you require in the file too.
Use a Textbox and just type it all in

[sintek]

Hi anhlu

This can get you started:
Will populate combobox with values from A2 till last populated row

Please Login or Register  to view this content.

Will populate TextBox with the value of ranking dependent on ComboBox1.selection

Please Login or Register  to view this content.

[anhlu]

Thanks! Please see attached file.

[sintek]

Hi anhlu

This solves your first issues...still not sure what you mean by the right dot showing up...but i assumed that it was dependent on the value in your columnP

[anhlu]

Thanks Sintek!
I'm guessing you added codes for column P (Performance) only? I'm very new to VBA so i really don't understand the codes you created but I'm very appreciative of your help!

My goal is to choose the contract on the form, then enter values for Rank, Renewal Options, Reprocured, Current Term (Start & End), Staffing Level - FTEs, and the values will appear on the correct cells on the spreadsheet.
For the Retention, Financial Results, Performance, and Customer Satisfaction, I want to choose the color on the form and the right icon will show up.

Can you review the attached again?

Many thanks!

[sintek]

This will get you started...i have not done all the optionButtons yet...