Form to change single cell value

[carics]

Hello Excellers,


I have been googling this for a while but I cannot find exactly what I look for and I am not good enough to adapt the few examples I have until now.


What I would need is to have a kind of a simple for to change the value of a single cell, based on the various options in a defined Name that would pop up in a dropdown list in that same form.


Using that:


1- clicking on a button on sheet1 opens the form

2- the form shows:

a) label = region

b) dropdown list based on "region1" dynamic ranged name (that I can do)

c) OK button to proceed

d) Cancel button to cancel

3- if the option is chosen and the OK button is pressed, cell A1 should now have the same value as the option we chose from the dropdown list


And that is all! I would guess that it would be easy even for me, but I see that I am not good enough for this...


Thank you for giving a look into it.


BR

 

[Hui]

@Carics

The Ok Button will have associated code like

[pre]

Private Sub CommandButton1_Click()
Range("A1").Value = ListBox1.Text
'or Range("A1").Value = ComboBox1.Text
UserForm1.Hide
End Sub

[/pre]

 

[carics]

Hi Hui!


Again, thanks a lot for the reply.


I still am too dumb to get it from your explanation.


I get to create a user form and design it with label and combobox. The points I do not get:


1- how can I open the form with the button on sheet1?

2- how can I fill the dropdown options with the dynamic name I created?

3- should I enter some code for the Cancel button?

 

[SirJB7]

Hi, carics!

Check this file, please:

http://www.2shared.com/file/sGHhG05h/Form_to_change_single_cell_val.html

It has a named range "TableRegion" which is loaded into the userform "ufRegion" to fill the dropdown list "lstRegion", two command buttons, and updates A1 cell.

Hope it is what you wanted.

Regards!

 

[carics]

Hi SirJB7!


That is perfect! I still had some trouble with the "click button to open form" thing, but I managed that after all. My new year's resolution should have been learn VBasic


Thanks a lot for your trouble, it will help me a lot!


Regards!