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
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.
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.
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?
I have several non-modal userforms in my App, some of them have date-fields that require manual entry typing of dd/mm/yy etc (No single userform has more than one date-box in it, this I think may be pivotally useful)
Now the Userform 'Calendar' that is built on the class of the same (cCalendar) name, has the write value line 'ActiveCell.value = theCal.value'
I'm looking to change this to refer to the correct userform.Textbox value, depending on which form is open.
I would imagine I could simply have a global string, whose value is set (or re-set) whenever a Userform is initialized (some sort of 'ActiveUF.value = Me.Name), where I get lost is referring to the components by name, so as to have a case statement by where I go:
Code:
Private Sub theCal_AfterUpdate()
Select Case ActiveUF
Case "AddForm"
application.vbe.components("AddForm").controls("AddFormDatePicker").value = theCal.value
Case "EditForm"
'.... etc
end select
end sub
better way of doing this (instead of passing around the userform name as a variable) - or proper syntax for referring to controls outside of the 'active' userform (but an open userform nonetheless)?
Every time I have to do this particular thing with userforms, I completely forget how, and the object browser always leads me on an infinite loop of Application.vbe.activevbproject.vbcomponents.vbe.active....
PS - there may be one slight complication to the process - one of the forms, has a 2-tab page in it, each page having similar (but named differently) fields. So I may need to be able to throw in 'Activepage' or whatnot
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 3 Replies View RelatedI 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.
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.
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
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 2 Replies View RelatedI've a worksheet, which has formulas, links, combo list box and format control.
I've made a user form to enter the data, so that the particular cells in the worksheet is filled and the remaining cells are calculated as per the formula.The format control (combo box) has list of range.
Is it possible to bring this format control (combo box) on the user form, so that without going to the sheet, I Can control the sheet by selecting the combo box values.
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.
I have a userform with multiple images
I would like to set all images to visible = false when images are dynamically named.
E.g. image names:
Image73om3
Image4y45h
Image12AB2
Is there a way to say
Code:
With all images
visible = false
End With
or
Code:
image*.visible = false
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 (I’ve 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.
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 2 Replies View RelatedI 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
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 2 Replies View RelatedThis 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& .....................
Userform : how to get combobx names in Ascending order
View 12 Replies View RelatedI am working an a userform. I want people to add records to the spreadsheet thru the UserForm ONLY, meaning that if they go straight to the spreadsheet they wont be able to edit anything.
View 1 Replies View RelatedMy final version will have more than 40 images with all the same code: they will all increment a corresponding SpinButton with 1.
[Code]....
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.
I have a user form where two command buttons are set with the following code:
Code:
Private Sub CommandButton1_Click()
Frame1.Visible = True
Frame2.Visible = False
[Code]....
Now, when cmdbtn1 is clicked, frame1 is visible and vice-versa.
However, the position of both the frames on the user form are not on the same position on the user form. Indeed, it takes the position of the design view.
I want a code which will place the frame1 and frame2 at the same place and position on user form, irrespective of its position in the design view.
What I missed. I already added the MS VBA Extensibility 5.3 on a VBA Reference. but still got an error.
Line1: Dim objForm as object
Line2: Set objForm = ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
Line3: Set Butn = objForm.Controls.Add("Forms.CommandButton.1")
The error is on Line3, "Runtime Error '91': Object Variable or With block variable not set".
I would like to use a PropertyGrid type control on my UserForm. Is there an effective way to wrap this control and create an add-in to use in VBA?
View 1 Replies View RelatedI 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.
How can I put the webbrowser disable so that is impossible to clik in it?
View 7 Replies View RelatedI 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 4 Replies View Relatedi'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 2 Replies View RelatedI 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.