Create control associated with row

[lazzer]

Hi,

I have a worksheet which generates another sheet. I would like to have several controls (buttons ideally) associated with each row in the generated sheet (so the user has an option of performing further work on these). These controls should have an addressable reference to their column (ie, without resulting to absolute addressing). If buttons won't do it, check boxes would be a good start! Again, dynamic creation, and some level of association with a row is required.

I've not done a lot of work with VBA before (you've probably worked that out already!) but I have done bit's with VB6. My old trick of creating an array of checkboxes (for example) didn't seem to work.

Any advice appreciated, thanks in advance.

Laz

[GTO]

Greetings and Salutations Laz,

I don't know about anyone else, and it is but one of the "Cool" things about this forum, but I marvel a bit that we get to "meet" folks in such faraway places. I feel far to new to 'officially' welcome you, but Welcome! anyways.

Since you've done VB6, you're already 'up' on me, but I thought to ask you to post some type of example workbook if possible. For those of us less imaginative as to the end result, you may well get better feedback.

Mark

[lazzer]

Hi GTO,

Thanks for the welcome and suggestion!

I've attached a workbook to this post. The "Configuration Sheet" is only used first time by the user to generate some of the data on the "Commissioning" Sheet. On the commissioning sheet, there will be space for the User to add lot's more data.

I'm configuring drives, so each drive's data will be stored in a row. After User input of other data, I would like the user to generate a text file with some scripting parameters (which can be imported to some third party software). I'd like to be able to do this on a row by row basis though, hence the need to generate a button.

I think I can manage the generation of the script and exporting to text file, it's the dynamic creaton and association of the controls that have me a little stumped!

Apologies if my terminology is throwing anyone off the scent! My VB6 days were a good 4 years ago!

Cheers,

Lazzer

[lazzer]

Hi GTO,

Thanks for the welcome and suggestion!

I've attached a workbook to this post. The "Configuration Sheet" is only used first time by the user to generate some of the data on the "Commissioning" Sheet. On the commissioning sheet, there will be space for the User to add lot's more data.

I'm configuring drives, so each drive's data will be stored in a row. After User input of other data, I would like the user to generate a text file with some scripting parameters (which can be imported to some third party software). I'd like to be able to do this on a row by row basis though, hence the need to generate a button.

I think I can manage the generation of the script and exporting to text file, it's the dynamic creaton and association of the controls that have me a little stumped!

Apologies if my terminology is throwing anyone off the scent! My VB6 days were a good 4 years ago!

Cheers,

Lazzer

[GTO]

Hi back Lazzer,

I was not being humble in stating that you'd be 'up' on me from the 'get', on top of which, its about 0430 hrs here and there's another fellow that I am still working on answers for.

While I am surprised to see several more advanced coders not signed on, I'm sure they'll answer soon.

As to the buttons, a 'sorry' and simplistic answer (that you may well be already past) is I'd look at looping something similar to:

Sheet2.OLEObjects.Add("Forms.CommandButton.1", ....

where prior to the creation of the button(s), I'd look at row/col top/left for the cell and ensure some equality/adjustment as necessary to assign the location for the new button.

I don't recall working with button creation on a sheet, so assigning the code to the programatically created button is well beyond me at the moment.

Probably little or no help, but I will certainly be following the thread to learn.

Have a great day,


Mark

[lazzer]

Actually GTO, that looks like it could be quite helpful. I picked this up yesterday, so I'm not at all familiar with the properties or methods available!

Is an active object the best way to accomplish this, or should I be doing it conditionally with user input ie:

Have a single 'make script' button, then look for the coditions of the document (using true/false under certain headings) to create the correct scripts?

All suggestions welcome - I'm quite enjoying getting back into this!

Again GTO, thanks!

[p45cal]

This is some code I played around with some time ago; I post it now (because it's past my bedtime) unadjusted to make it work with command buttons instead of checkboxes, but it might give you some ideas:
[vba]Sub Macro1() 'puts forms checkboxes in each of selected cells
Dim myRng As Range
For Each cll In Selection
Set myRng = cll
Set myCBox = myRng.Parent.CheckBoxes.Add(myRng.Left, myRng.Top, myRng.Width, myRng.Height)
myCBox.Placement = xlMoveAndSize
Next cll
End Sub
Sub Macro4() 'puts activex checkboxes in each of selected cells
Dim myRng As Range
For Each cll In Selection
Set myRng = cll
Set myCBox = myRng.Parent.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=myRng.Left, Top:=myRng.Top, Width:=myRng.Width, Height:= _
myRng.Height)
myCBox.Placement = xlMoveAndSize
Next cll
End Sub
Sub blah()
For Each cb In ActiveSheet.OLEObjects
If cb.progID = "Forms.CheckBox.1" Then If cb.TopLeftCell.Address = Selection.Address Then MsgBox cb.Name
Next cb
End Sub
Sub balh()
For Each shp In ActiveSheet.Shapes
Debug.Print shp.Name, shp.TopLeftCell.Address
Next shp
End Sub
[/vba]

[lazzer]

Ahhh, Thanks P45Cal. After I get through this mornings email I'll be trying that out!

Thanks to all for the good advice..