How do I code a userform

[HHD]

Hello Forum,

I am very new to excel and VBA, so please be gentle with me. Could someone advise me how to do the following on the attached worksheet.
I receive a spreadsheet, which has heaps of data, (sometimes 20000 rows) but the only columns I need use are D and AA starting in row 7. Basically what I need is to be able to select name, start time, end time from the drop downs on the userform, press submit and the name would be pasted into column AA all rows in from start time to end time as listed in column D, then repeat until end of rows.

I usually have to do this manual, but would like to automate it. What I do is take a name (These are on the Names sheet, and are defined as a range ? it is almost always a static length), and in column AA, I place that name in every row from a start time to a finish time, then another name under that and so on until the end of the rows. (every row must end up with a name in it). The times are in column D.

I have made a userform, with 3 comboboxes, but I only know how to add the range in the rowsource properties, and the range for time could be any length, so maybe it really needs to be named by code otherwise I need to define it each time. It would be nice if the each start time was to commence from the last end time, but that may be difficult. Well for me it is anyway as I haven?t even worked out how to paste the names into the combobox values in column AA yet.

I know this is a big ask, and if someone could start me off I would be most grateful

Thanks in advance

HHD

[Killian]

Hi and welcome to VBAX :hi:

You can use the Initialize event to define the ranges and set the rowsource properties. Here's a way of doing it - I've assumed the names start in cell A1 and are contiguous and the time always starts at D7 on it's sheet


Private Sub UserForm_Initialize()
Dim rngNames As Range
Dim rngFirstTime As Range
Set rngNames = Sheets("Names").Range("A1").CurrentRegion
rngNames.Name = "Names"
Set rngFirstTime = Sheets("ABC").Range("D7")
Set rngAllTimes = Sheets("ABC").Range(rngFirstTime, rngFirstTime.End(xlDown))
rngAllTimes.Name = "Times"
Me.cboNames.RowSource = rngNames.Name
Me.cboStartTimes.RowSource = rngAllTimes.Name
Me.cboEndTimes.RowSource = rngAllTimes.Name
End Sub

In terms of making it work, I soppose you just need to identify the range in column AA from the start time to th eend time and assign each cell with the name selected (?)


Private Sub CmbSubmit_Click()
Dim rngTarget As Range
Dim c As Range
Set rngTarget = Sheets("ABC").Range(rngAllTimes.Find(cboStartTimes), rngAllTimes.Find(cboEndTimes))
For Each c In rngTarget
rngTarget.Offset(0, 23).Value = cboNames
Next c
End Sub

Thats a bit rough and ready but it works.

Do you want to account for times and/or names being removed from the list when they've been used? It do-able on the comobox change events but it'll start getting tricky if you want to close and then start again part the way through.

[HHD]

Killian,

Thanks a treat, that works fine and will save me heaps of time.

All the Names would always need to be available on the list, (because they might be used several times), and I dont really need to clear the times, I was just being a bit lazy, because clearing them out would save me scrolling to find the new first time when entering subsequent names, but I can live with it as it is a big improvement to the way I used to have to enter the names.

You help is much appreciated :bow:

HHD

[Killian]

Your welcome...
I t's worth noting that I just used .Find on the range of times so it will work fine provided ali the times are unique (I assumed they are)
If that's ok then you can mark the thread as solved (from the thread tools menu at the top)

[ALe]

Here another very rough procedure (it's slow). Hope you'll find it useful.

[HHD]

Thank you ALe,

Your code is very useful and has shown me new ways of doing things.

As I am only new to VBA every time I read a code I am lucky because I gain knowledge from it.

I will now mark this post as solved.

Cheers
HHD