populate form field with selected cell 1

[dingleberry]

Hi All, I've written a bit of code to do some formatting. The problem I have is it is very rigid. I'm trying to add some flexibility. So instead of running a macro and having it just fly through, I'd like it to present a form. In this form, I'd like to populate a textbox with a selected cell from the spreadsheet. I have a form, and I know how to create a sub function onbutton_click but I have no idea how to make the focus go from the form and to the spreadsheet. Does this make sense? I'm simply trying to grab A12 for example and have A12 show up in a text box in a form.

 

[CautionMP]

dingleberry,
Maybe [tt]Application.InputBox()[/tt]? This provides a mechanism for your user to select a Cell (Range) and have it's address returned to you in VBA.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[dingleberry]

CautionMP, thanks. Is there a way to get inputbox() to look like the input boxes that are used when creating a formula in excel. You know the kind that look like a inputbox but at the right they have an icon to pick a cell?

 

[CautionMP]

dingleberry,
I think so, but I can't remember how. I think the functionality appeared in 2002 or 2003 and I can't remeber if it was a function, add-in, or possibly a dialog.

If I some time today I will do some searching to see if I can find it.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mikej336]

Just create a user form...

Here is a pretty good example.

http://www.exceltip.com/st/Create_User_Forms_in_Microsoft_Excel/629.html

Uncle Mike

 

[firefytr]

What you are talking about is a RefEdit box. Those allow you to select a range of cells. There is a button on the userform control toolbox that allows you to create one (on a default toolbox it's the bottom left-most item, hover over it will read RefEdit; the toolbox is viewed by VBE | View | Toolbox).

Here are some other resources on creating userforms...

http://www.contextures.com/xlUserForm01.html

http://www.mrexcel.com/tip009.shtml

http://j-walk.com/ss/excel/tips/tip76.htm

(programmatically)

http://support.microsoft.com/?kbid=158848

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[firefytr]

Btw, I forgot to mention, you can populate any object on a userform (ie textbox, label, combobox, refedit, etc) with the activecell by using the Userform_Initialize event.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[CautionMP]

firefytr,
That's the little bugger I was thinking of. Thank You! I will be able to sleep tonight.

CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[firefytr]

I will be able to sleep tonight.

LOL! Glad I was able to contribute to a good night's sleep. ;-)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[dingleberry]

Thanks team. I feel pretty silly now boy!