Check combos values and add if not already added

[VicA]

I've starting a new userform vba.in excel. I have a combo called Product which gets data from worksheet 2 dynamic named range. Product (range E:E)
If the product I am inserting in the combo is not listed I would like to add the new product and update the combo and also if it is a new product I would need to add the specific gravity of the product. also on the same worksheet as a dynamic named range SG, (range F:F.) then continue filling the other combo's and text boxes on the userform.

Vic

[werafa]

so you enter/select a value in one combobox.
If value exists, you populate the form with existing record data
if value does not exist, you create a new record.

Not sure about the SG question, as you must already have the product details entered to have this available as a lookup or match.

You will likely find value in reading up on Access userforms as this is a common task in MS Access and uses much of the same logic

Werafa

[VicA]

so you enter/select a value in one combobox.
If value exists, you populate the form with existing record data
if value does not exist, you create a new record.

Not sure about the SG question, as you must already have the product details entered to have this available as a lookup or match.

You will likely find value in reading up on Access userforms as this is a common task in MS Access and uses much of the same logic

Werafa

[VicA]

Thanks Werafa

Only started VBA last week so not proud I've what I've done so fa"r, So I would like to add a message to say This Product does not exist do you want to add it. "Yes/No". If Yes the problem then is for each Product there must be an SG. this will be used later to calculate a price in litres how do I get the user to add the SG range as well and then continue with filling the UserForm

[werafa]

Hi VicA,

we all start somewhere, so don't apologise. also, this is the one forum that I actively support - in large part due to the lack of assumed knowledge and willingness by the pros to help newbies.

Use msgBox to ask a basic question. Goggle "excel vba msgbox" for details
Use inputbox to get a user entered value via a popup dialogue: goggle "excel vba inputbox" for details

to give you a headstart, you might also wish to look up workbook open event (yes, just goggle "excel vba workbook open event")
this will allow you to trigger a sequence of events when you open the workbook (you could also use worksheet activate - "excel vba events" will give you the full list of options)

you will need to break your job down into sequential logic - eg if this then do that. each discrete step should be a self-contained subroutine/function, and subroutines should ideally be small enough to fit on one screen (not always practical, but a good goal). this allows you to break your code into bite-sized chunks and debug them accordingly

a second tip - investigate the use of range objects. you can identify the table row you are working with, define it as a range object, and then interact with it in ways such as

myRange(lRow,lCol).value = myValue

instead of

thisworkbook.worksheets("mySheet").range("A1").value=myValue

this is good coding practice, and will simplify your job a lot - as you can define the range as one step, and populate it with data as a separate step.

regards
Werafa