Form development question

[Matt Jensen]

Howdy
Is there anything wrong with creating an application by placing form
elements such as a text boxes directly onto a worksheet?
Cheers
Matt

 

[Guest]

Not that I can think of. I've done it more than once and I haven't had a
problem.

 

[Jim Cone]

Matt,

You may want to review "Using ActiveX controls on a document" in the VBA help file.
Also, you may want to consider the controls from the Forms toolbar found at...
View | Toolbars | Forms.
These are not as versatile, but easier to use with fewer problems.

Regards,
Jim Cone
San Francisco, USA

 

[Matt Jensen]

Great, thanks Guys.

I was wondering, what then does this code mean?
=EMBED("Forms.TextBox.1","")

To be more specific:
Does it mean I need to or can access the textbox as an object by way of
traversing the Forms object first?
Can I change/alter this code at all? eg. what can you put in the last ""
then? Also, when I try to change part of the forumla, it says invalid name
or object... What is a correct name or object?
Is there anything else I can do with the formula or use it in any other
ways?

Thanks
Cheers
Matt

 

[Jim Cone]

Matt,
It means you have an ActiveX textbox control embedded in the spreadsheet.
To work with the textbox, activate it by clicking it then right-click it and select properties.
That gives a you a multitude of options to configure.
If all else fails read "Using ActiveX Controls on Sheets" in vba help.
Regards,
Jim Cone
San Francisco, USA

 

[Harald Staff]

Hi Matt

Users won't be able to jump between the controls with the Tab button (unless
you wite specific code for it in each and every control) and optionbutton
won't group. Not wrong, just inconvenient at times. Let me reverse the
question; what do you need a turbo calculation engine and 16 million + cells
for in this form ?

HTH. Best wishes Harald

 

[Matt Jensen]

Thanks Jim
Yeah I'm familiar with what you say, I was just intrigued by the code in the
formula box for this ActiveX object and being an inquisitive type I wondered
if and how I could change it, or how it was if at all relevant to any VBA
coding...
Thanks
Matt

 

[Matt Jensen]

Yes I'd noted that problem about the Tab button... What is the relevant code
to replicate it?

It's hard to explain what I'm doing but it will be worthwhile doing it in
Excel I believe, although I don't have many other choices really (would've
rathered do it in a database to HTML via ASP or something but time and cost
constraints prevented it thanks to our austere IT department).
Essentially, I need to provide an interface for manipulation of tabular
data, and need it to look as sleek as possible, and can't have it in any
executable or database... Nevertheless, Excel will provide for a
suitable short term solution which is the aim..

So..
Your point though, is partly why I asked the question, because I was
wondering if I should or if it is possible to place the form elements inside
a form that would need to be 'inline' in my worksheet, instead of
'embedding' the elements in my worksheet. All of the examples of the uses of
forms that I've seen have used them as dialogue boxes that effectively
'popup', which is not what I want. What is want is part form, part table
data.

Thoughts?

Cheers
Matt

 

[Harald Staff]

Hi Matt

This makes perfect sense. Too many IT departments take pride in preventing
efficient use of computers. Ok, for tabbing, it's about trapping the press
of the Tab key, which has keycode 9 and to see if there's a Shift pressed
simoultaneously, if so go back instead of forward.
With Textbox1, Textbox2 and Commandbutton 1, in that tab order, code goes
like this:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 9 Then
If Shift > 0 Then
CommandButton1.Activate
Else
TextBox2.Activate
End If
End If
End Sub

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 9 Then
If Shift > 0 Then
TextBox1.Activate
Else
CommandButton1.Activate
End If
End If
End Sub

Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
If KeyCode = 9 Then
If Shift > 0 Then
TextBox2.Activate
Else
TextBox1.Activate
End If
End If
End Sub

It's a dirty job, but someone has to do it

HTH. Best wishes Harald

 

[Matt Jensen]

Cool -thanks Harold
I think I might create a single procedure for this for universal tab use,
with arguments to specify if a form element is 1st or last in a collection,
and if so, just to not "tab" anywhere, otherwise tab forward or backward...
Or I wonder, maybe the numeric number of form elements within a form could
be used instead...

As for my development, do you therefore think, to achieve something both
form and tabled data as I mentioned, that Excel worksheets with embedded
ActiveX elements is the best (and only?) option?
Or can a userform be embedded in a worksheet?

Cheers
Thanks
Matt

 

[Matt Jensen]

Sorry, Harald it should have been...
just habit
Matt

 

[Harald Staff]

As for my development, do you therefore think, to achieve something both
form and tabled data as I mentioned, that Excel worksheets with embedded
ActiveX elements is the best (and only?) option?
Or can a userform be embedded in a worksheet?

Hi Matt

You can't embed a userform, it floats over the application. Which is usually
for the good. I believe you are on the right track from what you describe. A
worksheet with activex controls is obvious, easy to use, requires no special
installation on the clients, and extremely powerful. Good luck with your
project.

Best wishes Harald