Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Remove UserForm Control From Tab Order


can i disable/remove a control from the being in the tab order.
i no i can move to the bottom but there is too many too use that option.
i need it to be elimated from user input alltogether.
it is filled automatically


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Programmatically Remove A Control From Userform
Suppose I have a userform named myUserForm and a checkbox on it named myCheckBox. How on earth do I remove myCheckBox from myUserForm at run time? I've tried every which way I can think and I always get "error 444". Some things I've tried are:

myUserForm.Controls.Remove "myCheckBox"
myUserForm.Controls.Remove(myCheckBox)
myUserForm.Controls("myCheckBox").Remove
myUserForm.Controls.RemoveControls "myCheckBox"

I've put this in a standard module, the ThisWorkbook module, even in the myUserForm code module and each time it's the same error.

View Replies!   View Related
Control Order Of Class Object Events
Split from Determine Order Of Event Procedures

I realise that there's no set order for different types of event, because, as you say, it basically depends on what the user does. But what I'd like to control is what order like events fire in.

So let's say that object_1 handles the aplApp_WorkbookNewSheet event (an application-wide event).

Let's say that object_2 is an object of the same type as object_1, and therefore also handles the aplApp_WorkbookNewSheet event.

The objects are entirely independent; they know nothing about each other. However, I would like to be able to control whether the aplApp_WorkbookNewSheet event is fired first in object_1 or first in object_2 when the user triggers this event by adding a new worksheet somewhere.

View Replies!   View Related
Macro That Deletes Sheet With Control & Shows UserForm Causes UserForm To Disappear
This is weird - if you delete a sheet that contained a control then

a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End
b. public variables lose their value

These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?


View Replies!   View Related
Remove Picture From Image Control
I know how to load a picture onto an image control using VBA code, but how do I clear the picture? (NOTE: I am not talking about using the properties window in the VBE, I am talking about coding in VB)

View Replies!   View Related
Tab Order In Userform
I have several checkbox and adjacent to it there is a textbox. Also I have a command button.
On Form initialize, all checkbox and textbox are hidden, except first check box and textbox.
The first checkbox1 is enabled and textbox1 is empty. On Exit event of textbox1, the checkbox2 and textbox2 gets visible, with checkbox2 enabled.

On exit event of textbox1, the cursor goes to command button on Tab
My requirement:
The cursor should go to textbox2.

Note:- I have Tabindex set to as following:
Textbox1 = 0
Textbox2 = 1
Commandbutton1 = 2


View Replies!   View Related
Delete/Remove Picture From Image Control
I want to delete a picture from an image control in a worksheet when pressing a button. So my code is:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Image1.Picture = LoadPicture("")
End Sub

Well, the problem is that this code only works when I create it. If I save and close the excel file when I open it again and I press the CommandButton1 I get the following error: Run-time error '-2147417848(80010108)' Method "Picture of object "IImage" failed.

View Replies!   View Related
Add/Remove Custom Control To Chart Drop-Down Menu
I want to add a control under Chart on the menu bar. This line of code errors with "Invalid Procedure Call or Argument"

With Application. CommandBars("Worksheet menu bar").Controls("Chart")

Change "Chart" to "File" or "Edit" or "Tools" and it's fine.

The Chart item only appears when a chart is selected, but it errors even when a chart on the worksheet is selected.

View Replies!   View Related
Set Order Of Userform Controls Loop
I am using stuff from loops http://www.ozgrid.com/VBA/control-loop.htm to loop through all the controls on a UserForm. But I can't find a way of controlling the order in which this is done. I tried both TabIndex and moving the controls around without success. Is there a property of a control that controls the loop order.

View Replies!   View Related
Toolbar Control On Userform ..
I've got the bare bones of a toolbox control on my userform but I can seem to find anyway to refer to the buttons and control them (Ive tried using the properties page but it doesn't seem to give me the options I need). I want to be able to add a macro and a picture to the buttons, is this possible?

Eg.

With Userform1.Toolbar1.button("x")

.image/picture/faceid = x
.action = "macro1"

end with

I've looked for ages on the board but can only find threads on creating toolbars in the application, not on userforms.

View Replies!   View Related
Cursor Control With Userform
I have created a simpel userform that shows sales total and it is activated [.show function] whenever an entry is made in the order column. All of this works fine.

The only problem I have is that the curser jumps in to the text box and doesn't return to the order column where next entry needs to be made.

How do you move the curser out of the userform, back to the activesheet?
Ideally it should move to the next cell for the user to make entry.

I am attaching my sample file here. It has some odd things that I was just playing around with as learning experience. But the main question is how to control the curser.

View Replies!   View Related
Filling Control On Another UserForm
I am trying to add a list of items to a combobox on form1 from form2 where form 1 has a variable name.

I can use:

x = UserForms(0).Name
VBA.UserForms.Add(x).show

to show a userform with a variable name but I can't get it to add anything to the combobox using this

VBA.UserForms(x).Combobox1.Additem

View Replies!   View Related
Userform Control Blink
This code works fine on a normal userform But I declared the userform with "New" in my calling procedure

myForm As New form

How can I get it to work??
Or more interesting, why is it not working
Also what exactly is the advantage of using the keyword New
I have propertys set in the userform, but other than that..
Can someone tell me if the load function in this case is better or not

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub cmd_click
Call blink
End Sub

Public Sub Blink()
Dim i As Long
For i = 1 To 20

' set color
Form.cmd.BackColor = &HFFFFFF
DoEvents
Call Sleep(60)

Form.cmd.BackColor = &HFF& .....................

View Replies!   View Related
UserForm Control Looping
Split off from Loop Through ActiveX TextBox Controls On Worksheet. Here is a zip file with the files in it. The main one that I am working on now is PO Form.xls. I want to start with the Qty column and copy any values that are entered into the form to the file called PO.xls in the Qty column of this file.

View Replies!   View Related
Hyperlink UserForm Control
I am looking for Hyperlink Control or any other control that I cam add to my form. When I move mouse on it and press it open to me attachment.

View Replies!   View Related
UserForm Control TextBox Or ComboBox
If this control is a TextBox, I would like to read the Text property,
and if it's a ComboBox, I would like to read the Value property.

Public Function readValue(c As Control) As String
If (TypeName(c) = "TextBox") Then
' convert the Control to TextBox then put readValue = c.Text
Else
If (TypeName(c) = "ComboBox") Then
'convert the Control to ComboBox then put readValue = c.Value
End If
End If
End Function

View Replies!   View Related
To Set Focus To The RefEdit Control In Userform
I am trying to implement a simple userform using the RefEdit control.

So I have the RefEdit control and an Ok (which has code attached to it) and a Cancel commandbutton.

For some reason, I can't get the focus on the RefEdit control (i.e. when I activate the form, I have to actually click in the RefEdit box before it gets the cursor). Which property sets the focus in this control?

Right now I have the Ok button Default property set to True.

I have a commandbutton on the spreadsheet that activates the userform.

View Replies!   View Related
UserForm Control To Select Range
I want a control to select a range in a userform I'm creating.

I wonder if we can use the same type of control the wizards use, where you click on the right and choose the range? Alternatively, what's the best option?

View Replies!   View Related
Setfocus From Module To Userform Control
I have a project with many textboxes on different forms. On some of the textbox_exits on some of the forms, I call a standard module that checks what the user has input. If the user needs to change the input, a msgbox appears to inform the user the info needs to be changed.

I have tried to use ControlSource, Name etc., But I cannot seem to setfocus back onto which ever textbox the input needs to be changed. I think it has something to do wih the _exit event, but not sure.

View Replies!   View Related
UserForm Control Not Showing Fully
I have a workbook which when opened connects to an external datasource (excel file) and downloads a table of information. While this is happening I have a userform which shows which has a lable saying (connecting to datasource, please wait....). However, when I open the workbook, the userform shows but the labels do not show, the userform just appears white.

View Replies!   View Related
Enter Key On UserForm To Next Control Not Working
I have a User Form that is used to collect data. CommandButton1 loads the User Form data into the worksheet and CommandButton2 clears all data from the Form in preparation for entering the next record. Immediately after a user first opens the Form, the very first time CommandButton1 is clicked, the Enter Key stops working. At this point data can be typed into any TextBox on the Form, but when the Enter Key is pressed the cursor remains in the TextBox. (The Enter Key will not move the focus to the next Textbox in the Tab Order. Also, if I alter the code to set the focus on a CommandButton as the active control instead of a TextBox, pressing Enter on the active CommandButton does not execute the CommandButton's macro... the same behavior as a TextBox; nothing at all happens when the Enter Key is pressed even though the CommandButton has focus).

At the point when the Enter Key stops functioning, if the user presses 'Alt-Tab' to leave the Form and then immediately uses 'Alt-Tab' to return back into the Form the Enter Key suddenly works again and continues to work correctly from that point on even after CommandButton1 is clicked. Again, the Enter Key stops working ONLY the first time CommandButton1 is run immediately following initially opening the workbook and Alt-Tabing into the Form immediately corrects it. I saw one other post in this forum with this same problem in 2003, but unfortunately it did not get answered.

View Replies!   View Related
Disable WebBrowser Control On UserForm
How can I put the webbrowser disable so that is impossible to clik in it?

View Replies!   View Related
Control Toolbox Pops-Up When UserForm Run
i've gotta demo an Excel app in a few days and this never really bothered me, but someone recently pointed out that it can get distracting for the Control Toolbox to pop in and out randomly. the form is being built for use by other staff, and i suppose it would also be distracting for them if the toolbox arbitrarily appeared here and there. is there a way to turn it off?

View Replies!   View Related
Force UserForm Controls On Top Of Image Control
i have a textbox on top of a picture box but i cannot replicate it with a new textbox or combobox.
when i place the control on the picture box it disapears?


View Replies!   View Related
Userform Call Macro On Control Change
Is there a way to call a subroutine anytime any control is changed within a userform?

For example,

I have a userform that needs to go through and add/multiply almost every value in every textbox and also add together values associated with checkboxs. I need this to happen anytime a textbox value is changed or a checkbox is checked.

View Replies!   View Related
Move To Next UserForm Control With Down Arrow Key Press
I am trying to setup a shortcut key while in a userform so you can press the page down key and the focus will jump to an "Ok" or "Close" button.

I tried the following keypress procedure without any luck.

Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = vbKeyPageDown Then
Me.btnClose.SetFocus
End If
End Sub

I even tried a similar test on a textbox control wihtout luck.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = vbKeyPageDown Then
MsgBox "You Pressed PageDown Key"
End If
End Sub

I should say that this is a form that has a multi-page control on it and the showmodal property set to false.

It's almost like the event doesn't even fire. Does anyone have any ideas on how to get this to work?

View Replies!   View Related
Caption Of All CommandButtons In UserForm Frame Control
I have frame on a userform with command buttons. Is it possible to loop thru and capture the command button captions in a textbox. I'm trying to put together keyboard on a userform, so the user can populate a textbox with letters or phrases.

View Replies!   View Related
Pass UserForm Control Values To Sub Procedures
I have been trying to use a multi-select listbox as the argument for a subroutine. For some reason, I keep getting a run time error, type mismatch. I'm baffled because when I define the specific listbox, it works fine, but when I attempt to pass the listbox I get an error.

Public Sub OtherDirectTotal(Expense As ComboBox, CostCat As ComboBox, _
Cost As TextBox, Years As Msforms.listbox)
Dim IDC As Double
Dim IDCt As Double
IDC = Val(UserForm2.txtIDC) / 100
If Years.Selected(0) = True Then
If Expense = "Sponsored" Then
If UserForm2.ChkODC = False Then
Select Case CostCat......................

View Replies!   View Related
Filter Worksheet Data With UserForm Control
I have a database in Sheet1, and need to make a drop-down list for each column title, so that it would filter out the necessary letters or words. E.g. I type "au" and see the words starting "au" in one single list (like aura, aubergine, etc..)

View Replies!   View Related
Evaluate UserForm Control Where Name Is In 1 Cell & Property In Another
In A1, other code inserts the name of a Form control (textBox, comboBox etc), and in B1 it inserts the name of a Property that is always valid for the A1 control. I want to concatenate these two items to produce a formula in C1 that evaluates the current value of the Property on the running form and continues to update with each recalc. It's ok if I have to force a recalc to get the latest values.

The code feeding the items to A1 and B1 and which will be harvesting the Property values from C1 is running in the same Form that holds the controls being referenced.
So if A1= "Label1" and B1 = "BackColor", then in C1 I'd like the same result as if in VBA I said X = Label1.backcolor. I think that what I need is the Evaluate function, but I've read Arron's article on it I just can't seem to make it work here.

View Replies!   View Related
Collapsible Multi-Select UserForm Control
I would like to add a combobox type control that will allow multiple selections. I know that the combobox in the MS Forms Object Library only allows single selections and that the list box is the way to go to allow multiple selections, but I am trying to keep the form as small as possible. I would like to add a control similar to the one used on pivot charts, where the user clicks the dropdown box and can select multiple items. I'm hoping this control is installed with MS Office and just needs a reference to it, but I am open to installing third party controls as well.

View Replies!   View Related
Control Toolbox Appearing On Multipage Userform
I've started experiencing a strange problem. The control toolbox keeps appearing when the first page on a multipage userform is activated. I can't figure out what's causing this, and after searching the forum, with no luck, I thought I would see if anybody else is experiencing the same problem, and how to fix it.

View Replies!   View Related
Place Code Behind Userform: Active Control Colored
I have created a user form and I am trying to get the active control colored. I am using the code from Mr Excel's VBA book - starts on page 454, and I have entered all the code, but now when I activate the user form I get an error ....."Compile Error. Invalid attribute in Sub or Function." and when I click OK, it takes me to this line of code.....

Private WithEvents objForm As clsCtlColor

In the book, this is where I am supposed to start entering the code "behind the userform" rather than in the class module. So, I assume this means that this code goes with all the other code for the user form (in VBA project click on form, then view code). Am I wrong? Should I be adding a module? Not sure what I am doing wrong.

Here is the code I have in the class module....

Public Event GetFocus()
Public Event LostFocus(ByVal strCtrl As String)
Private strPreCtr As String

Public Sub CheckActiveCtrl(objForm As MSForms.UserForm).......


View Replies!   View Related
Control Button Error :: Suppose To Call A Userform
I have a control button on a worksheet that is suppose to call a userform. I am using the code

Frm_Customs.show

But I keep getting an error. The button was created from the control toolbox.

View Replies!   View Related
Set Text & Hide Control On UserForm Show/Load
I'm trying to do is set up a form load event to initlize some controls. Here is my

Private Sub Form_Load()
'Initialize the form
lblProcessing.Visible = False
txtFileName.Text = "Enter a file name"
End Sub

At the moment, this event is not triggering. I have the code in the code behind my form - should it be in a module? PS: This site is great - it's answered a lot of my other questions so far without me having to make any posts.

View Replies!   View Related
Load Worksheet Picture Into UserForm Image Control
I have an image box on a userform in Excel VB. Is there ANY WAY to load an image into this image box from an object that I have loaded into an excel worksheet something like

If userform1.checkbox1=True Then
userform1.image.picture = loadpicture (Worksheets("Sheet1").shapes("Object 1"))
ElseIf userform1.checkbox2=True Then
userform1.image.picture = loadpicture (Worksheets("Sheet1").shapes("Object 2"))
Else
msgbox "No image"
Endif

View Replies!   View Related
Add / Remove Userform
I've created this simple form to add and remove data in a combobox
in a userform and i want to add and remove items

note: I've tried to do that but the new added items does't appear
next time i run my workbook

View Replies!   View Related
Pass Calendar Control Date To Selected TextBox On Another UserForm
I have a workbook that has multiple spreadsheets. Data is added to the spreadsheets using userforms for each spreadsheet. I have created a pop-up calendar to add dates and want to know if there is a way to add dates without having to create multiple calendars for each txt field on each userform. I would like it so that when I click on the calendar it would put the date into the text field it was launched from.

This is the code in the calendar which only puts the date into the field named Dat_Clsd.

Private Sub CMD_Close_Click() .....

View Replies!   View Related
Access Control Properties Of Controls Within UserForm, MultiPage & Frame
I want to access the Properties of a number of controls in a running form, and these controls may or may not be contained in a Frame or a MultiPage.

In particular I want the Top and Left for these controls, which means I have to first find out if the control is contained in a Frame or MultiPage so I can get the reference for Top and Left. I'm ok with doing this for controls inside a Frame, but the MultiPage is eluding me. I get an error when I try to access these controls and it looks like they are actually owned by the individual Pages of the MultiPage.

how do I find out if a given control is contained in a given MultiPage?

Validate UserForm MultiPage and Frame Controls

View Replies!   View Related
Remove UserForm On Close
I have a function (courtesy of Walkanbach) to create a userform, very useful as it means I keep memory down as I can create the UF's as I go and tweak to suit.

I have been trying to get the userform to be removed when the user closes it. The close action in this case is the X button top right as this userform only has the userform itself and a listbox.

When I set the code for the event procedure it doesn't error, but nothing happens. The microsoft help pages seem to suggest that you can't remove a userform through vba, but I find that hard to believe. I've tried variations on the close procedure and all to no avail so far.

Dim xInt As Integer
With TempForm.CodeModule
xInt = .CountOfLines
.InsertLines xInt + 1, "Private Sub TempForm_Terminate()"
.InsertLines xInt + 2, "ThisWorkbook.VBProject.VBComponents.Remove TempForm"
.InsertLines xInt + 3, "End Sub"
End With

View Replies!   View Related
Remove Title Bar From A UserForm
Is it possible to take the headers of a UserForm. I mean the bit that can have a form name and the little cross on the top right hand corner.

View Replies!   View Related
Remove Title Bar From UserForm
how to remove the title bars on userforms? I cant work it out for the life of me.

View Replies!   View Related
Remove X Close On UserForm. Multiple Versions
I've found a link on OzGrid "How to remove the (X) CloseButton from Userforms" but I can't seem to get it to work with Excel 2003. The code looks like it was written for Excel '95, '97 and 2000.

View Replies!   View Related
Remove Part Name/Caption From UserForm Controls
UserForm1
Current OptionButton names are like ABC123, ABC 124, etc.

How can i change all names, with code - as i am not seeing a find and replace option within UserForms, to all but the "ABC" part, the result of the OptionButton names shall therefor be 123, 124, etc. - delete "ABC" or, find "ABC" and replace with

View Replies!   View Related
Macro To Set The Order In Which Worksheets Should Be Printed (order Is Changing Daily)
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:

I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?

Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...

Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).

Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.

View Replies!   View Related
Re-Order Columns Based On Column Order Of Another Worksheet
I need a way to re-order an excel worksheets columns based on another worksheet.

Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.

How can i reorder the columns without physically having to cut and paste the columns to match?

I have attached a sample spreadsheet.

You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.

View Replies!   View Related
Userform To Control Macro Features. Macro = Search For File Type
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.

The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.

I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet

I just don't have any idea how to sync the two.

If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.

The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.

'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.

'CB1_Find_Files' should activate the macro to find any files for the specified criteria.

There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.

View Replies!   View Related
UserForm Navigation Bar Control Error "1004"
I am having some difficulty with user form navigation bar - when scrolling to the end of the data it works just fine. When scrolling towards the beginning of the data it breaks with a "Runtime 1004: Application-defined or object-defined error".
The reason may be that the beginning of the data is not correctly defined. The data has a header row. Code that I think is relevant to the navigation bar follows:

Private Sub Navigator_Change()
'When Scrollbar value changes, save current record and load
'record number corresponding to scroll bar value
'Call SaveRecord
Set RangeData = Range("CONTACT").Rows(Navigator.Value)
Call LoadRecord
End Sub

Private Sub UserForm_Initialize()
'Load 1st record in CUSTOMER and initialize scroll bar
With Range("CONTACT")
Set RangeData = .Rows(2)
Call LoadRecord
Navigator.Value = 2
Navigator.Max = .Rows.Count
End With
End Sub...........................

View Replies!   View Related
Control Scroll Bars: Remove The Scroll Bars From Just One Worksheet But When Unticked
In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?

View Replies!   View Related
Select Next Control / Cell On Worksheet After Enter In Control
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?

View Replies!   View Related
Drag From Treeview Control To Spreadsheet Control
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.

I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.

I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved