ComboBox - Using Combo Boxes to Change/Delete rows

[Trubble]

Hi all,

I hope someone can help or atleast point me in the right direction.

I've been working on a spreadsheet that uses forms to fill out a front sheet and to also fill out a sheet that would be used as data in a mail merge document.

As this 'project' has developed the spreadsheet has gotten more and more complicated. I've gotten thus far by changing snippets of code here and there and in some cases nothing fancy just brute force!

The current problem that I'm having is using a combo box to select an item by their name (NCP Name) on the DATA INPUT sheet and be edited or deleted (in the example file it would be the Yellow button for edit).

If the example file isn't enough I can include the whole file if it helps.

I think I've reached my current limit with VBA and my brain is hurting and could do with some help.

Thanks in advance

T

[Ken Puls]

Hi Trubble,

I think your example file is missing something... There's no code in there, the first sheet is blank, and the second sheet has a little bit of data on it, but not much.

Were you trying to upload more than that?

[Trubble]

Hi Paul,

Sorry about that, I think my slimmed down version was perhaps a little too slim.

Here is the full version.

I've had a think about tackling the problem and am going to try and put something together, but I would still be interested in any help

Thanks

T

[Bob Phillips]

Can you explain the problem in more detail. When I click that yellow button, there is nothing in the list, and the data sheet is empty. What exactly is the problem.

[Trubble]

Hi,

Basically thats the problem. I haven't managed to come up with an idea yet.

From the sheet you can see that there is a button for entering the information and another to edit the information.

I can't think of a way of selecting a line, based on its NCP name and editing it.

What I was thinking and am working on right now, is a form that opens up when you click on the yellow button, lets you select a name from a combo box, then opens another form which will then edit the information of the selected name.

I looked at a couple of different examples, the Hay Deliveries example for one, which was on here, but I can't find anything in it that helps.

Thanks

T

Edit :

This code goes into the form that opens after the yellow button is clicked

[VBA]Private Sub CommandButton1_Click()
formEdit.Show
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim x As String
ActiveWorkbook.Sheets("NCP DATA").Activate
Range("E2").Select
Do
If IsEmpty(ActiveCell) = False Then
x = ActiveCell.Value
cboNCPEdit.AddItem x
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

End Sub[/VBA]

[Bob Phillips]

I still don't think I am getting what the issue is, but wouldn't you just set the Rowsource to NCP as you do on the parent form?

[Trubble]

Hi,

Like I said I'm not too hot on visual basic and a lot of the time it goes over my head.

I've managed to solve it, albeit in a rather unwieldy and cumbersome manner.

If anyone wants to have a look at the finished product and make comment then feel free.

Thanks much

T

[Bob Phillips]

I can't see any difference. What have you done?

[Trubble]

Hi,

I've tweaked it slightly.

With the Edit button, what ever NCP name you select it will transpose the information from the NCP DATA sheet into a new form where it can be edited and update the list.

I've also included a list box so you can see what has already been input.

Thanks

T

[Ken Puls]

Interesting...

I'm not sure I totally follow the logic of what you want to do here, but I'd avoid the approach of opening another userform to do the edits. You then have two forms to maintain, which is a pain.

Since you have a listbox on UserForm1, which not make it so that when a user clicks the "Edit" button, it checks for whatever customer is highlighted, then populates all the fields with their data. Your Enter button then become and Enter/Update button that looks for the NCP row in your table. If it doesn't find it, it adds a new record. If it does, it places the current information from the form.

Does that sound like what you're after?

[Trubble]

Ken

Using the list box sounds pretty much what I was looking for but I'm not too sure how the selected item in the list box can be used (I understand how it should work but not how to actually do it).

The whole project is based around producing a front sheet and mail merge information with the same format of information.

I'm not sure if i'm over complicating a pretty simple project, but i'm going to keep plodding away.

As an add note, is there a way to limit combo boxes so that only the listed information can be used?

Thanks again

T

[Bob Phillips]

Just set the Style property to frmStyleDropDownList

[Ken Puls]

Hi Trubble,

Have you checked out our KB for examples? Try these to get you started:
Identify Selected Item in a single selection Listbox
Identify the selected item in a combobox

Give it a try, and post back when you get stuck.

[Trubble]

Thanks XLD.

Another quickie. Is there a reason that when I try to perform a mailmerge, even just using a blank document, the word document can't find any of the named ranges on the second sheet (NCP DATA)?

Thanks again

T