Excel Macro/ Combo Box help

[farazbaqar92]

Hi,

I am working on an analysis sheet that i have created to review & analyse the RFP responses.

I need an automated way to populate the responses from the supplier.
The suppliers use an standard template to provide the responses. I already have a macro to copy the relevant data.

What I need to achieve is,

Have a Button (Macro or whatever) to,
1. Give me a prompt with a dropdown list of suppliers that are actual suppliers and not the dummy record mentioned as "Enter Supplier Name" (the list of suppliers is populated in the workbook sheet called "Suppliers"
2. Upon cancelling or selecting NO, do nothing.
3. Upon selecting the supplier & Pressing Yes, the macro should be able to jump to the relevant sheet ("Compiled responses")
4. Search the name of the selected supplier (in 1) in values of the sheet
5. Move 2 cells down
6. Paste special (values only) {the response had already been copied using the earlier macro as i stated}

This function or button should be available in the "Recommendation sheet" and hence once the paste is done, the sheet should not be changed and rather the user should stay in the same sheet.

Also, if possible please explain in a bit detail as i am particularly new with this.

Thanks in advance

 

[hotabae]

This is a bit of a project you've got here, and I won't do it all for you, but I'll give you some steps to get you started. Personally, I would use a fairly simple userForm.
Create a button to call a userform which is linked to code like this in a general module:

Sub button1_Click()
    UserForm1.Show
End Sub

Build a userform with:
a comboBox("enter supplier name")
a "Yes" button
a "No" button
And your code for the UserForm (stored WITHIN the userForm module) should follow this general structure:

It should have at least 4 "chunks" of code - Initialize (populating the combobox) , commandButton1_Click (yes), commandButton2_Click (no) , and QueryClose (what happens when the close "x" is clicked)

Private Sub UserForm_Initialize()

'Initialize form starting position to keep the form on the starting screen.
Me.StartUpPosition = 0
Me.Top = Application.Top + (Application.UsableHeight / 2) - (Me.height / 2)
Me.Left = Application.Left + (Application.UsableWidth / 2) - (Me.width / 2)
    
'
'YOU WILL NEED TO ADD THE SUPPLIERS TO THE COMBOBOX SOMEHOW (lots of ways)
'
Dim supplier As RANGE
Dim supplierRange As Range
set supplierRange = Sheets("Suppliers").Range(.....)
    
'ONCE YOU ESTABLISH A RANGE/ARRAY OF SUPPLIER VALUES...

'Add all the Non-Zero Suppliers to the comboBox
For Each supplier In supplierRange

  If Not LenB(supplier.value) = 0 Then

   Me.comboBox1.AddItem supplier.value

  End IF
  
Next supplier
  
End Sub

'=======================================================================
Private Sub CommandButton1_Click()

'Figure out where you want to send the info and "Set" it here
Dim ws As Worksheet
Set ws = Sheets("Compiled responses")

'Collect the info as save it
Dim infoVariable As String
infoVariable = comboBox1.value

'Freeze screen while writing info to worksheet
Application.ScreenUpdating = False

'Use the comboBox selection to update a worksheet

'Restore screen updating function
Application.ScreenUpdating = True
End Sub

[LEFT][COLOR=#222222][FONT=Verdana]'=======================================================================[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Private Sub CommandButton2_Click()[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    'Kill the form[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Unload Me[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR][/LEFT]

'=======================================================================
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    'If user clicked the 'x'...
    If CloseMode = vbFormControlMenu Then
        
        'Check to make sure they wanted to.
        If MsgBox("Do you want to close?", vbYesNo + vbExclamation, _
            "Close Form?") = vbYes Then
                        
            'Kill the form
            Unload Me
            
        'If they DIDN'T WANT TO CLOSE...
        Else
            'Stops trying to kill the form
            Cancel = True
        
        End If
        
    End If
End Sub

Edit: Little disclaimer, this is butchered copypasta from some of my own code. It definitely doesn't work right now but it is sort of a sample/roadmap. I hope my commenting can improve understanding.