Can we retain changes made to a form during runtime?

Yajiv · Joined: 26 Feb 2008 Posts: 1

I have a userform in excel vba. I want to add buttons during runtime
as per user requirements.
I also want those buttons to be present when the user open it again.
is it possible?

Archived from group: microsoft>public>vb>general>discussion

MP · Joined: 04 Oct 2007 Posts: 203

You'll get better help on the .excel.programming group

"Yajiv" wrote in message @e23g2000prf.googlegroups.com...
>I have a userform in excel vba. I want to add buttons during runtime
> as per user requirements.
> I also want those buttons to be present when the user open it again.
> is it possible?

depending on exactly what you want to do, yes.
using me.controls.add you can add controls at runtime
using microsoft VisualBasic for Applications Extensibility 5.3 reference you
can write code on the fly and save it to your userform module

Karl E. Peterson · Joined: 04 Oct 2007 Posts: 4836

Yajiv wrote:
> I have a userform in excel vba. I want to add buttons during runtime
> as per user requirements.

Is this something you know to be possible? (I have no idea, myself, on this point.)

> I also want those buttons to be present when the user open it again.
> is it possible?

Sure, given the above. Just store your requirements somewhere, and read them as
needed.
--
..NET: It's About Trust!
http://vfred.mvps.org

MP · Joined: 04 Oct 2007 Posts: 203

"Karl E. Peterson" wrote in message
news:%234m$7lKeIHA.4704@TK2MSFTNGP03.phx.gbl...
> Yajiv wrote:
>> I have a userform in excel vba. I want to add buttons during runtime
>> as per user requirements.
>
> Is this something you know to be possible? (I have no idea, myself, on
> this point.)
>
>> I also want those buttons to be present when the user open it again.
>> is it possible?
>
> Sure, given the above. Just store your requirements somewhere, and read
> them as needed.
> --
> .NET: It's About Trust!
> http://vfred.mvps.org

fwiw it is possible to add controls at runtime(similar to vb)
I assume the op was talking about commandbuttons

but how to get at the _click event ?
Smile

I guess one could use vbe to rewrite the form code, save and reload the new
form, before calling f.show???

Public Sub AddCmdsRuntime(f As UserForm)
Dim lidx As Long, oCmd As CommandButton

For lidx = 0 To 5
Set oCmd = f.Controls.Add("Forms.CommandButton.1", "Cmd" & CStr(lidx),
True)
With oCmd
.Left = 10
.top = 15 * (lidx + 1)
LogEntry "Top " & .top

.Height = 10
.Width = 50
.Visible = True
.Caption = "Cmd" & CStr(lidx)
End With
Next lidx

End Sub
mark

Karl E. Peterson · Joined: 04 Oct 2007 Posts: 4836

MP wrote:
> "Karl E. Peterson" wrote ...
>> Yajiv wrote:
>>> I have a userform in excel vba. I want to add buttons during runtime
>>> as per user requirements.
>>
>> Is this something you know to be possible? (I have no idea, myself, on
>> this point.)
>>
>>> I also want those buttons to be present when the user open it again.
>>> is it possible?
>>
>> Sure, given the above. Just store your requirements somewhere, and read
>> them as needed.
>
> fwiw it is possible to add controls at runtime(similar to vb)
> I assume the op was talking about commandbuttons
>
> but how to get at the _click event ?
> Smile

Given there's no control arrays, that's the $64k question, isn't it! Beats me.

> I guess one could use vbe to rewrite the form code, save and reload the new
> form, before calling f.show???

Ouch! Sounds pretty nasty.
--
..NET: It's About Trust!
http://vfred.mvps.org