Userform help needed - matching combox value to worksheet identifiers

[AGrace]

Hi all,

I'm working on a new man power vs workload calculator in excel for work.

I've built the overall design and various userforms, cell formula's etc but I'm a little stuck on the complex VBA i need to implement for the document to actually work as design.

As you can see from the image above, I have a Project Details Form. This form has three comboboxes that retrieve data from the DataTables worksheet. All the data on the DataTables sheet is persistent baring the Project names, as they will always change. All other data, TesterID's, Test Types, Workload Slots etc will always be the same.

Ok, so the problem I have is I need to know how to get the userform to find and match the slot number found on the "Calculator" sheet with the slot number the user has selected on the userform.

From there, I would like the userform to populate the corresponding cells to the right of the selected slot with the other data from the userform; Project name and Test Stage.

For example; the user opens the userform. Selects "Slot_01" from the workload slot #, and then selects "Dumby Project 1" from the "Project" drop down. And finally "1st Full QA" from the "Test Stage" drop down. If the user then hits "Submit", the above data should be populated in Row 11, in cells B11, D11 and F11.

If anyone can help me with this, I'd be most grateful.

Please ignore the "Durations" section of the userform. I can probably work that out once I have the other part sorted.

Many Thanks,

Adam

 

[DonkeyOte]

comments from note at EF.com echoed here

http://www.excelforum.com/excel-pro...ng-combox-value-to-worksheet-identifiers.html

 

[AGrace]

Hello DonkeyOte, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
As I replied on EF.com, I am sorry I violated that forums rules. I apologised on that thread. I couldn't see anything in this forum's rules that clearly stated no multi-forum question answering. Apologies to MrExcel if there is a rule regarding multi-forum posts, I must have simply missed it.<o></o>
<o></o>
The main reason I asked on both forums is I can understand and appreciate that not everyone has time to help out with all queries, so rather than post on the one and hope for the best I tried doubling my chances of a response. <o></o>
<o></o>
However, now both posts have been marked as breaking the rules, I guess neither will receive replies. <o></o>
<o></o>
<o></o>

 

[DonkeyOte]

I'm sure you will get a resolution on either/or ... or even both.

Neither forum bans users for x-posting (some do), however, both would ask you disclose links so people can check to see if it's been resolved elsewhere before posting on it.

The whys etc are more eloquently outlined by Ken Puls: http://www.excelguru.ca/node/7

 

[AGrace]

I've now changed the userform slightly. The Slot combobox is populated by the slot range on the sheet1 (calculator). <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
All I really need help with is tell the user form to see the value in the combobox, where that value is on the worksheet and then insert the other combobox values, offset slightly so that they're entered into the correct columns (B11, D11 and F11).<o></o>
<o></o>
If someone would be as kind as to showing/telling me how to do that, I'll do my best to cobble the rest together. <o></o>
<o></o>
Cheers<o></o>
<o> </o>

 

[snowblizz]

I've now changed the userform slightly. The Slot combobox is populated by the slot range on the sheet1 (calculator). <o></o>
<o></o>
All I really need help with is tell the user form to see the value in the combobox, where that value is on the worksheet and then insert the other combobox values, offset slightly so that they're entered into the correct columns (B11, D11 and F11).<o></o>
<o></o>
If someone would be as kind as to showing/telling me how to do that, I'll do my best to cobble the rest together. <o></o>
<o></o>
Cheers<o></o>
<o></o>

I think you should be able to use the Find method. Something like this which is loosely based on the helpfile's example.

With Worksheets(1).Range("a1:a500")
    Set c = .Find(userform1.combobox1.Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        c.Offset(0, 1).Value = userform1.combobox2.Value 'Column B, offset 1 column, same row as found item
        c.Offset(0, 3).Value = userform1.combobox3.Value 'Column D, offset 3 columns (from A), same row as found item
    End If
End With<code></code>

 

[AGrace]

Thank you so much Snowblizz! That worked marvelously. I've now altered that slightly so that the Durations "Add" buttons work in the same manner!

There's one thing I've noticed that I would ideally like to amend, and that is preventing the submit button from functioning if any of the Comboboxes are empty - to prevent the user from accidentally adding just the slot assignment or project and no test stage etc.

Would you know what I'd need to add to your code example to get this to function this way?

many thanks again!!

 

[snowblizz]

Depends a bit on how the form is set up. I do have a idea though.

Disable the Submit button in its properties.

add this sub to the userform, (and adjust to suit your controls)

Sub enableSubmit()
If combobox1.Value <> "" And combobox2.Value <> "" And combobox3.Value <> "" And combobox4.Value <> "" Then
CommandButton1.Enabled = True
End If
End Sub

and in each combobox event place a call,

call enableSubmit

at the end of the code

this would then, hopefully, each time the user changes a value run the test and enable the button when all fields have a value.

 

[AGrace]

Hey Snowblizz,

Thanks again for such a swift reply. This one however has me a little stumped... Not sure where these bits go tbh :-/

Sorry, I'm not that savvy with VBA at the moment, kind of learning as I go.

With regards to the first part; do I simply add this to the userform code or the submit button code?

My code looks like this at the moment:

Sub enableSubmit()
If WorkSlotBox.Value <> "" And ProjectBox.Value <> "" And TestStageBox.Value <> "" Then
SubmitBtn.Enabled = True
End If
End Sub

I assume this isn't functioning at the moment because I'm unsure where to place the "call enableSubmit" code??

I thought maybe I had to add it to a change instance:

Private Sub ProjectBox_Change()
Call enableSubmit
End Sub

but that didn't prevent the values from being added.

Note that this isn't throwing up any failures, it's simply functioning as it did before, ergo entering in values even if not all comboboxes are populated.

Sorry for taking up your time on this, but I do really appreciate your help.

 

[AGrace]

[NOW SOLVED]

Sorry, was being incredibly special... completely forgot to set the submit button to disabled in its properties!!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Works like a charm!! <o></o>
<o></o>
Sir, you are a star. <o></o>