New to user forms

[GO GaryFingers]

I've never used user forms b4 today.

Question is- Can the form 'live', or show on a worksheet?

I have several Active X controls on Sheets, That are a pain.I've been told to get these controls onto a user form. (the controls are activated unwantingly with changes in worksheets)

On my first form, I made a sub -myform.show on This workbook_open sub
and , Whala! it appears to my delight, in the upper left corner of screen ( I set postition to windows default 3). The form appears to be outside of the Excel Application window itself!

Which module, and what code is best to load/show the form (on a sheet)?

 

[Legacy 98055]

I don't understand. Do you wish for the userform to be centered on the worksheet area? Where exactly?

 

[GO GaryFingers]

I Mean that when you go to a worksheet, the form is always there, at a certain position.
other sheets, I don't need it. (It can be in the way).

I'm using the form mostly to run macros, but I do want to utilize them for housing the activeX controls, which do various functions for me.

I suppose I have the wrong concept for these forms. But I'm sure they can be quite useful when properly employed.

I noticed, that the form, when your'e running a macro , and during debuging and go into break mode, they disappear. so I made a command button on a worksheet to xxxx.show it.

There must be intelligent code to handle all of this.

 

[Legacy 98055]

Gary. I think you are expecting the userform to behave as an embedded creation on your worksheet. That is not the behavior for a userform. It is an altogether "other" window. Look up the definition of an MDI form and let me know if that is what you are expecting. If so, I can't really help you...

 

[GO GaryFingers]

I see now, I was expecting an embedded object, since that's the only kind I've used.

Thanx for your help.

I think I will move my active x controls to a form or forms, because of past experience with these controls (combo boxes) code being activated w/ any change to worksheet.

I've managed with if then statements to try to isolate them, but when I'm debugging other routines, I see that these control codes are each, sometimes visited or looked at.
what a waste. and sometimes the code is activated, with unwanted results.

Do you Know of some source Example excel books, with forms being used for me to see how a form expert may use them?

 

[Smitty]

I think I will move my active x controls to a form or forms, because of past experience with these controls (combo boxes) code being activated w/ any change to worksheet.

I've managed with if then statements to try to isolate them, but when I'm debugging other routines, I see that these control codes are each, sometimes visited or looked at. what a waste. and sometimes the code is activated, with unwanted results.

Do you Know of some source Example excel books, with forms being used for me to see how a form expert may use them?

I'm not sure what you mean that your objects run when a sheet is changed. The only way that's going to happen is if you code it that way. Can you give an example of an instance where a sheet change causes an ActiveX control to take unwanted action?

ActiveX controls in a sheet are generally very robust, and I've never had any issue with working with them, which is part of my confusion.

As for information on forms, you can find some tutorials via Google search. You can PM me and I can send you a workbook with loads of examples.

HTH,

 

[GO GaryFingers]

Smitty, Ok,
I'm not well versed in these forums, Is it ok to post a macro code here?

Most of the controls I use are private sub in the worksheet module
This one, has values passed to it, so it's a Public sub.

The list fill range, is on a different book, and part of my main list of Items,
all on one sheet called "Task"
I use a different control just before using this one, which sets the named range text of the desired Category,...(one of about 45, ea with as many as 300 rows, some only 25)

then that's passed on to this control to set the list fill range property for the catg. picked

All the If statments, are trying to keep it from being activated when I've made changes to the list fill range, which is on the main sheet, that I use every day

Most of the controls I use, have list fill ranges from the worksheets
It makes them Dynamic, or current with my work

This set up works pretty well,
but, sometimes, one of the controls is activated, of course, when there was a change to the worksheet to where it's list fill range is.
I'm sure there's better ways, But I've been totally going this alone.

(I have no Idea what ME does, but I've been told before to use it)

Public Sub Pricebox_Click()
    
    'new one, see priceboxold below)  exit tests, set up this one to pass to sub module, faster??
    'youre in col d
    
    If Me.Pricebox.ListIndex <> 0 And ActiveCell.Column = 4 And ActiveSheet.Name = "WKSht Table" Then
    
        Dim lrng As Range
        Dim rw, cn2, trw As Integer, a, b As Variant
        
        cn2 = Range("vbxno").Value
        
        Set lrng = Workbooks("Mat.xls").Sheets("Task").Range("P" & cn2 & "List")
        rw = Me.Pricebox.ListIndex
        trw = lrng.Row + rw
        Me.Pricebox.ListIndex = 0
        
   'go over to col A now, newone, not using, a, b
        ActiveCell.Offset(0, -3).Range("A1").Select
        Selection.Activate
        
        Call RangeCopyFast2(trw, a, b)
    End If

End Sub

 

[Smitty]

It's fine to post your code, just try to use CODE tags put [ code ] (no spaces) before your code and [ / code ] (no spaces) after your code.

I would imagine that some of your problem lies with selecting/activating, which is rarely necessary:

ActiveCell.Offset(0, -3).Range("A1").Select
Selection.Activate

Depending on what RangeCopyFast does you can do it all without ever selecting or activating.

 

[GO GaryFingers]

Good observation.
I've since learned that you don't have to select an item to change it, or get data from it.
I should go back to simplify or update a lot of it.
I have older code in many places.

But I do think the control can be activated, when a sheet changes, if the List fill range for the control is affected, unless I otherwise cancel out the action with the if/then conditions.

But can this still happen from a form?

I suppose I should get really good retrieving data anyways.

One problem, is the format I originally set up, doesn't have a sorted no'. system at col 1, but my work around is to use Index w/ match functions, which are pretty powerful.

One challenge I can't figure out, :
I use a lot of Formatting ( as I'm sure is common), And would like to know how to get row # , and or count of all cells in a range that are Grey, (Interior.Index = 15)without doing a loop.??
I have done it with a newly discovered array variable, but I have to use a For each statement to populate it.

 

[GO GaryFingers]

PS.

I had an excel "expert", ( he seemed to Know a lot), years ago, in a different forum, tell me that he doesn't recommend the active x controls on worksheets, "they're problematic".

That's where I get it from