I have been trying to setfocus to any other object on my userform with absolutely no luck at all?
In the code below I open a dialog box to import a text file (with CommandButton1), if no file is selected the user is prompted, this works fine. However it leaves focus to the textbox and the user would have to click on some other object and re-enter the textbox to open the dialog box again.
I'm having some problems with SetFocus on a user form that I am using. Code is Below.
Dim Answer As Variant
With cboAccount
If .Value "" And .ListIndex = -1 Then
Answer = MsgBox(cboAccount.Value & " is not a registered account, would you like to add it?", vbYesNo)
If Answer = vbYes Then
Load frmNewAccount
frmNewAccount.txtAccountName.Value = frmEnterTransaction.cboAccount.Value
frmNewAccount.Show
If Answer = vbNo Then
cboAccount.SetFocus
End If
End If
End If
End With
End Sub
basically the code asks if the if the answer placed in a cboAccount (combo box) is valid against the list designated to that combo box,
If the entry is not valid a message box appears asking if they would like to add the entry to the valid list if they do not want to do this they can click no and in which case I wan't the focus to be set back to the combo box however currently the setfocus command above does not does not work and the focus is set to the next text box.
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 two userforms. When the user chooses the choice "Other" in the userform frmTradeSickOther, another userform called frmOther will be called. At this point, the user will enter some required comments in a textbox (called txtOther) and click the "Add Comment" button. If the user does not enter anything, a prompt will inform the user to type something in the textbox.
My problems: Once the user clicks okay to the prompt, frmOther is called back. When it's called back, I want the cursor to blink in the textbox. Eventhough I have SetFocus on the textbox, it does not work. I don't know why.How do I prevent the user from just hitting enter (i.e., accepting blank lines as comments) and bypassing the commenting part. The code below accounts for hitting enter once (i.e., one blank line), but not multiple times. How do I do this?
Here is what I have when the user clicks on the "Other" choice in frmTradeSickOther:
Code:
Private Sub optOther_Click()
Unload Me
frmOther.Show
frmOther.txtOther.SetFocus 'This gets the cursor to blink in textOther when frmOther is called.
End Sub
Here is what I have when the user clicks the "Add Comment" button on frmOther:
Code:
Private Sub btnAddComment_Click()
On Error Resume Next
txtOther.Value = Trim(txtOther.Value) 'This is to make sure spaces aren't accepted as a comment.
If Me.txtOther = vbNullString Then
Me.Hide
MsgBox "Please enter the necessary information.", vbInformation, "Required Field"
Me.Show
Me.txtOther.SetFocus 'Here is where I set focus on txtOther.
Exit Sub
End If
'Everything below is to add comments automatically to each cell when a value is changed.
Application.CommandBars("Cell").Controls("Delete Comment").Enabled = True
If Not ActiveCell.Comment Is Nothing Then
Application.CommandBars("Cell").Controls("Edit Comment").Enabled = True
Range(curCell).ClearComments
[Code] .......
I've got a userform with multiple pages, and in my userform initialize sub I set the focus to "combobox1" on "page1". When a user is on page2 of the form and clicks my "ClearData" button, which calls the initialize event, I get a run time error 2110 - "Can't move focus... etc" I would like users to have the ability to clear the form and start over no matter what page they are on.
View 8 Replies View RelatedI want to add a website script to UserForm , WebBrowser Control.
[Code] .......
How Can I add this script to WebBrowser as VBA Code?
I was just trying to work around a problem with multiple UserForms in project. I have assigned Object variables oUserForm1 and oUserForm2 to represent UserForms of specific names.
VB:
Dim oUserForm1 As Object
Dim oUserForm2 As Object
Set oUserForm1 = VBA.UserForms.Add("Data" & CStr(X))
oUserForm1.Y = Y
[Code] .....
When oUserForm1 shows new data are inserted and another macro runs with a line to hide the opened UserForm.
In my project manager this user form name is i.e. Data1 (for x = 1), but the syntax Data1.Hide returns error. After that line it ask me to close the TOP most modal UserForm.
But When I use Me.Hide all works well.
My question is: Does VBA not see the name of the userform ("Data1") because I have used the oUserForm1 variable to give it a focus to it?
Here is the scoop.
I have a userform that has a lot of textboxes that are formatted as date fields. Some of them have a default value and are locked=true, enabled=false. For those ones I have a checkbox next to them so the user can unlock and enable the textbox if they so desired.
Well the checkboxes and textboxes have a similar naming scheme, but are not numeric (they are not CheckBox1, CheckBox2, etc). As an example they one grouping is Cust1RelExpUnlock (this is the checkbox) and Cust1RelExpDateBox (this is the textbox). The difference in their name is the last portion (Unlock or DateBox).
Now when the checkbox is selected the code I want looks something like this:
Code:
Private Sub Cust1RelExpUnlock_Click()UnlockDateBox Cust1RelExpUnlockEnd Sub
Where UnlockDateBox is the function I'm having difficulty with and is supposed to be generic enough to work on any grouping assuming my naming scheme is consistent.
Using a combination of the Left() and Len() functions I can get the unique aspects of the name. Then concatenate it with "DateBox" as required. However I'm getting type mismatch errors, or object required erros. I tried various combinations of Dim _____ As Object, As Control, As Textbox... with no success.
I'm getting the correct name as a string, how do I make it work so I can reference the textbox.enabled/.locked?
Code:
Private Sub UnlockDateBox(Ck)
Dim CkDate As Control 'Tried control, textbox, object
Set CkDate = ProjectInputForm.MultiPage1.Object ' tried just using ProjectInputForm, and Object
Dim CkName As String
[Code] .........
I have a userform with 10 rows of data with 8 colums in each row... I am trying to figure out how to dynamically address the object names (ie Textboxes).
Short and simply let's say I have 10 Text boxes named textbox1, textbox2, textbox3, etc
I want to address them in a loop as follows
Private Sub Clear_Fields()
Dim field As Object
For x = 1 To 10
field = "textbox" & LTrim(Str(x))
field.Value = "1"
Next x
End Sub
Obviously I'm missing something here as I get the error Object Variable or With Block Variable not set
In design mode on my UserForm, I have an object on top of all other objects (it's hidden until a button is clicked). That's how I designed it and it was working fine when I ran the form. Now, when I run the form and click the button, the object appears at the bottom below all other objects. I've tried closing Excel & re-opening it, setting the ZOrder in design mode and in the CommandButton code--nothing works. If I did do something to cause this--I have no idea what it was. I've been moving and re-sizing some objects, but not adjusting the ZOrder.
View 9 Replies View RelatedI know there is something I am missing here and I can't really find the solution I need in past threads. I have a macro that calls about three macros in a row, then once it has done those tasks, I want it to show a particular userform using the userform.show statement. However, when it comes to show the userform I get an error saying that it cannot find the specified object? This is quite frustrating and I think I fixed it in another part a while ago, but I can't remember how.
View 2 Replies View RelatedI have a code that runs when I open the workbook. It asks if this is a new quote, if it is it should then open userform1. The problem is that sometimes when I click yes I get a "Path/File access error" popup, and then a VBA error box with "Run-time error '75' Could not find the specified object".
When i debug, it highlights the line "userform1.show"
IF i end, and then run it from within VBA it will then run fine.
Sometimes when I open the workbook it will just crash excel, and when I reopen it, it runs fine.
Simple code -
Sub Workbook_Open()
answer = MsgBox("Is this a new quote?", vbYesNo)
If answer = vbNo Then
Else
userform1.show
End If
End Sub
I have been working on large project using Excel VBA for several days. My code seems to be working correctly, but I have more to do and now, when I drag an object from the toolbox onto a UserForm, the object is not added to the list of objects on the form. If I go back to versions of the project that I was working on several days ago, there is no problem. If I run "Workbook Rebuilder", the objects that I have dragged onto the form are then added to the object list, but I still can't add new objects to forms from within the VBA editor. Is the project corrupted, or is there some other explanation, and are there any fixes? The code runs about 50 pages, and there are over 20 forms, so redoing from scratch is only a last resort option.
View 3 Replies View RelatedFollowing code from net and applied.
But runtime error on the highlighted line: "object doesn't support this property or method
Code:
Private Sub cbAddImage_Click() With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.ButtonName = "Submit"
.Title = "Select an image file"
.Filters.Add "Image", "*.gif; *.jpg; *.jpeg", 1
[Code] .......
I have the below to check for a numeric entry and when it's not, it deletes the entry, shows a Splash Form for a few seconds telling the user to enter Numeric, and then unloads.
The problem is, on the SetFocus part. I thought that it should put the cursor back into TextBox2 for another entry. It doesn't, in fact the cursor is nowhere to be seen. I have to grab the mouse and click in the TB2 to reenter a number.
Private Sub TextBox2_Change()
'//Check for numeric entry
If Not IsNumeric(TextBox2) Then
Me.TextBox2 = ""
'//Show SplashForm if not numeric
SplashForm.Show
Me.TextBox2.SetFocus
End If
End Sub
if i write the following function on a checkbox,
View 10 Replies View RelatedExcel 2003. User form coding an If statement the "Else" FieldName.Setfocus. The User form has a MultiPage object in addition to the above (setfocus) in a particular field (NOT on the MultiPage object) I want it open to Page2 instead of what ever page it was last closed on. I tried this: MultiPage1.Page2.Enabled
View 2 Replies View RelatedI have been working for 8-1/2 hours to get the focus to move to a specified control on my user form if criteria are met in two other controls on the same user form. It's not responding as I expect. I think I know what is happening; I just don't know why.
Situation: I have a user form with the following relevant fields in this tab order - LMonthDay, tbMonthDay, SBMonthDay, LYear, tbYear, SBYear, LTime, and tbTime. I used the designations L, tb, and SB in my control names to represent label, text box, and spin button respectively.
Problem: Once the SBMonthDay control has the focus, if I press TAB without changing the control's value, it does not execute the Exit event statement the way I anticipated. I want the focus to move to the tbTime control when the tbMonthDay & tbYear controls are already properly populated. (They will already be populated for 99% of the entries in this case.)
Private Sub SBMonthDay_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Debug.Print "SBMonthDay_Exit Begin"
If ISLIKE(tbMonthDay.text, "####") _
And ISLIKE(tbYear.text, "####") Then
tbTime.SetFocus
Else: tbYear.SetFocus
End If
Debug.Print "SBMonthDay_Exit End"
End Sub ...........................................
I have a userform with a multipage consisting of 4 tabs.
Each one has several combobox's and 1 commandbutton.
As you move through the form the commandbutton moves the data to my worksheet then moves you to the next page. When it reaches the last page it will return you to the first page then unload the form which is great.
The problem I am having is the cursor is not in any of the boxes on the first page. If I click on one of the other tabs then return to first page the first combobox has the focus. All the tab orders are correct. Can anyone point me in the right direction.
Private Sub cmdNext3_Click()
ActiveWorkbook.Sheets("CarDetails").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
I am using a Textbox1_Exit event that (if certain values are true) sets the focus to different Textbox. However, when I invoke the Textbox3.SetFocus, it redoes all the code in the Textbox1_Exit event since it's technically leaving now.
How do I stop this from happening and ensure that the Exit event happens once? OR How do I properly SetFocus inside an Exit event?
I would like point to a cell in a spreadsheet, click a Command Button and copy the content of this cell to another cell, say D30.
View 2 Replies View RelatedHow to setfocus on a cell, when user press enter, key up, or tab ?
examp. :
i want setfocus to cell A5, when i press enter or key up on cell A2.
I've got a user form with 12 textbox, one for each month a total textbox number 13 and a 14th text box (TB28) to enter $'s in.
AfterUpdate in each textbox 1-12 posts its value to a worksheet all values are summed in a =sum range which in turn populates textbox 13. When TB13.text = 100.00 it sucessfully calls a number of routines but what I can't get it to do is set focus on TB28.
Private Sub TextBox13_Change()
If TextBox13.Text = "100.00" Then
With FormPhasedAmt.TextBox28
.SelStart = 0
.SelLength = Len(TextBox28.Text)
.SetFocus
End With
End If
End Sub
It returns a Run time error Unexpected call to method or property access highlighting .SetFocus. The rest of the code works OK. Is it because other textbox on the same user form have text highlighted because of the tab order?
In Excel 2000 there seems to be a problem with setting the focus in a UserForm TextBox if the SetFocus method is applied in the same submodule as the UserForm.Show method. Microsoft's circumvention for this is to put the SetFocus command in the form's Activate event submodule.
This seems to work OK except when the TextBox you are applying the SetFocus to is the first control inside a frame. It works if the TextBox is not the first control, and it can be circumvented by first setting the focus on a subsequent control then switching the focus to the intended control.
However, the circumvention is not that useful if there is only one TextBox control in a frame or, as in my current project, if you try to create a generic piece of code to validate controls from multiple forms and set the focus from within the generic code.
Does anyone know of a way over overcoming this problem and being able to directly set the focus to the first TextBox inside a frame?
I'm getting the following error:
"Object library invalid or contains references to object definitions that could not be found"
I wasn't getting that error last night and I'm not sure what I may have done to cause this error.
It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.
I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.
Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.
2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?
3) am i going about this the wrong way with the objects? should i be after vba code?
I am having difficulty getting a form to work the way that I would like it to work. I have a form that is used to display questions that my students will be answering. The form also is used to put the answers into a worksheet. I have 2 sheets. Sheet2 has the questions, student answers, and correct answers. Sheet1 is used to indicate correct answers and to keep track of percentage correct. I am fairly new to VB. I have 2 pieces of code that I am going to post. The first one works and the second one doesn't.
This is in the "This Workbook" section and it works.
Option Explicit
Public intNoQ As Integer
Public strNoQ As String
Public NumberofQuestions As Integer
Dim StudentName As String
Dim InputBoxAnswer As String
Public Sub Workbook_Open()
Application.Visible = False 'Hide Excel
Load Questions
Load NumberCorrect
NumberofQuestions = Worksheets("Sheet1").Range("K3").Value
For intNoQ = 1 To NumberofQuestions
strNoQ = VBA.CStr(intNoQ)
If intNoQ = 1 Then
Questions.Controls("QuestionNumberBox").Value = "Question#" & strNoQ
Questions.Controls("QuestionBox").Value = Worksheets("Sheet2").Range("B1").Value
End If
Questions.Controls("CorrectBox" & strNoQ).Visible = True
Questions.Controls("CorrectLabel" & strNoQ).Visible = True
Next intNoQ...............
I have created a userform and a command button to bring up the user form but when I click on the command button and the user form pops up I am not able to enter any data, the entire page freezes
This is the code
Private Sub CommandButtoncancel_Click()
unloadme
End Sub
Private Sub CommandButtonOK_Click()
With Workbooks("RETS results version 2.xlsm")
[Code] ......
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 one userform that loads combobox values upon userform Initialize. Though through a second userform changes can be made to anotherworkbook this workbook is saves any changes. when i close the second userform i need to rerun the 1st userform Initialize event to update the combobox's incase changes have been made.
View 5 Replies View Related
