UserForm help please

[Mel Smith]

Hello folks,

I'd appreciate some help, please, in setting up a UserForm on a spreadsheet that I'm building. In Cells B5 to B44 I have a list of names and in cells H4 to NH4 I have a list of dates.

What I would like to achieve is a UserForm with 3 'boxes' with a dropdown list, the first to select their name (B4:B44) and the second and third (both H4:NH4) to select the start and finish of a date range so that the user can then click a submit button and their selected dates are then shown on the spreadsheet in the relevant cells with the word "Applied" with a tan cell background.

Your help and guidance will be much appreciated.

Many thanks.

Mel

 

[Sektor]

Initialization code behind UserForm.

Private Sub UserForm_Initialize()
    With Sheets("Sheet1")
        cmbName.List = WorksheetFunction.Transpose(.Range("B4:B44"))
        cmbDate1.List = WorksheetFunction.Transpose(.Range("H4:NH44"))
        cmbDate2.List = WorksheetFunction.Transpose(.Range("H4:NH44"))
    End With
End Sub

 

[Mel Smith]

I copied your code into the 'Module' but the drop down boxes remain blank. What do you suggest?

Mel

 

[Sektor]

This would fill comboboxes with values.

 

[Mel Smith]

I can only get the UserForm to show from within VBA so I must be missing something as they remain steadfastly blank?

This is the code from the Module:

Sub userform1_open()
UserForm1.Show
End Sub

Private Sub UserForm_Initialize()
With Sheets("Calendar")
cmbName.List = WorksheetFunction.Transpose(.Range("B4:B44"))
cmbDate1.List = WorksheetFunction.Transpose(.Range("H4:NH344"))
cmbDate2.List = WorksheetFunction.Transpose(.Range("H4:NH44"))
End With
End Sub

Mel

 

[Norie]

Mel

Not 100% sure what you want but perhaps something like the below.

Note this will only work if it is a userform you are using, not controls on a worksheet.

It also assumes 3 comboboxes, named cmbName, cmbStart and CmbEnd, and a command button cmdAdd.

Anyway here's the code.

Option Explicit
 
Private Sub cmbStart_Change()
Dim rngEndDates As Range
    If cmbName.ListIndex <> -1 And cmbStart.ListIndex <> -1 Then
        cmbEnd.Enabled = True
        Set rngEndDates = Range(Cells(4, cmbStart.ListIndex + 14), Range("NH4"))
        rngEndDates.Name = "EndDates"
        cmbEnd.List = Application.Transpose(rngEndDates.Value)
    End If
End Sub
 
Private Sub cmdAdd_Click()
Dim rngDates As Range
Dim StartCol As Long
Dim EndCol As Long
 
    If cmbEnd.ListIndex <> -1 And cmbStart.ListIndex <> -1 Then
 
        StartCol = cmbStart.ListIndex + Range("StartDates").Cells(1, 1).Column
 
        EndCol = cmbEnd.ListIndex + Range("EndDates").Cells(1, 1).Column
 
        Set rngDates = Range(Cells(cmbName.ListIndex + 5, StartCol), Cells
(cmbName.ListIndex + 5, EndCol))
 
        rngDates.Value = cmbName.Value
 
        rngDates.Interior.Color = 52479 ' more orange than tan
        
        Unload Me
 
        UserForm1.Show
 
    End If

End Sub
 
Private Sub UserForm_Initialize()
Dim rngNames As Range
Dim rngStartDates As Range
 
    Set rngNames = Range("B5:B44")
    
    rngNames.Name = "NameList"
 
    Set rngStartDates = Range("N4:NH4")
 
    rngStartDates.Name = "StartDates"
 
    cmbName.List = rngNames.Value
 
    cmbStart.List = Application.Transpose(Range("StartDates").Value)
 
End Sub

 

[Mel Smith]

Hi Norrie,

Thanks for getting back to me. The following line is shown as red, any idea why that might be?

Set rngDates = Range(Cells(cmbName.ListIndex + 5, StartCol), Cells
(cmbName.ListIndex + 5, EndCol))

Mel

 

[Norie]

Mel

That's a kind of typo I made when I copied the code here.

I was trying to space the line of codes, you seem to lose that when you post.

Anyway, I must have accidentally hit Enter somewhere.

You just need to put the cursor right at the start of the 2nd line (it is 2 lines and 2 errors) and hit delete.

It should look like this, all on one big, long line.

        Set rngDates = Range(Cells(cmbName.ListIndex + 5, StartCol), Cells(cmbName.ListIndex + 5, EndCol))

By the way I didn't include any sheet references, you didn't mention a sheet name.

If you start the form when the sheet with the names etc is active it should work.

If that sheet isn't active then it won' work, or the comboboxes will be populated wrongly.

That can be fixed by adding sheet references.

 

[Mel Smith]

Hi Norrie,

Thanks for getting back to me. I've sorted out the red line but I cannot get the user form to show when the workbook opens but only when I run it from the code screen. I'd like it to show on Sheet 1 which is entitled Calendar. What lines of code do I need to insert, and where, please?

I do appreciate your help.

Mel

 

[Norie]

To get the form to show just add this to the workbook module:

Private Sub Workbook_Open()
       Application.Goto ThisWorkbook.Worksheets("Calendar").Range("B5"), True
       UserForm1.Show
End Sub

Change UserForm1 to the name of your form.