VBA - Userform: Click In A Text Box, Contents Is Selected
Mar 20, 2007
I'm building a userform (for the first time), and I'm working with a textbox control. I'd like to make it so that when the user clicks in that textbox, the contents is selected, so that if they begin typing, it will type over what is already there.
My question, specifically, is whether or not there is a property I can set to make this the default behavior, and if not - what event do I tie to this action? Is it the "Enter" event?
What I have now, which will probably be unacceptable to my users, is code that looks like this:
Private Sub tbName_Enter()
ActiveControl.Value = ""
This makes the contents disappear, but I'd rather they stay there and just become "selected".
I want to color a row of cells a certain color when the user clicks on one of 5 buttons. If it is the "Purple" button I want the current row colored purple. The row has 5 cells in it from 1 to 5. I am having trouble finding the answer to knowing what row the current selection is. I can't find a way to pass the current row, col data to my routine. BTW, I recently changed from Office 97 to Office 2007. How do I set the properties of the button? I tried right click and properties but that only brings up the Worksheet properties. The properties button in the menu bar only brings up a font properties dialog.
I have an XY plot where the source X value data is a series of time values, each associated with a descriptive text cell. I would like to select a single point (obviously by mouse) and then execute a macro (right click preferred but a separate command button would be a suitable substitute for now) that will extract the X-value. I then plan to use that value to find within the source data the associated descriptive text cell and make a data label from that text. No clue where to start...the macro recorder is woefully deficient in this area.
this is my first post in this great forum. let me start talking about what I need, I used to convert some statement from PDF to XLS format for my work purpose. sometimes I am facing a problem that some text in the statement will come in more than one row, actually I need it to come in one cell but it is coming in several rows that mean, if I want to sort or filter my data, I will lose some important data. What do I want???
I want a macro to merge the contents of selected cells and put it in the first cell of the selected cells then clear the other cells. I will use this macro for the following cases: Case 1- assume we have this data in any sheet.
B1=How C1=are D1=you? ( all without any space )
after selecting the said cells and applying this macro, the result will be:
B1=How are you? ( one space only between the content of the selected cells ) C1=D1=empty
Hope this is fairly easy to solve. I have Screen #1 with a listbox with a CLICK event. The event populates a combobox below (with items based on listbox's selection). Listbox is NOT multi-select. Code runs fine manually, i.e., user opens screen selects from listbox, then can select from combobox.
I now what to open and make selections from another form/screen, Screen #2. I've written code to select the proper item from Screen #2's listbox but this does NOT trigger the listbox's CLICK event for me so the combobox isn't populated so I can then make that selection from Screen #1, also. I've tried setting focus to listbox first, then making selection, but that doesn't work.
QUESTION: Is there code that selects from a listbox in a way that mimics the user clicking the selected item in the listbox?
The alternatives I can think of are: 1) Change Screen #2's listbox code from CLICK to CHANGE event, but I'd rather not. 2) Move CLICK event code to sub-procedure and then call from both listbox CLICK and Screen #2 code 3) Some sendkey string like ENTER?
Would be easier to just mimic the user click, if possible.
With frm_Screen2 'Select item type from listbox With .LBox_Items
I am using this code to hide or unhide rows of text on another sheet:
VB: Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String Dim sValue As String
'Get the address of the cell that changed without '$' signs sAddress = Target.Address(False, False)
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB: If Target.Value = "Not Pursuing" Then ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
I have an array, let’s say B1:D20. I would like to be able to manually select any cell within this array and then run a macro which will place the contents of the selected cell into the cell in column A in the same row as my selection.
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:
1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case
2. the ToC has to change depending on the sheets selected.
I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.
What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).
Is there any way to have a picture pop up when a label or textbox is clicked in a userform? I need the userform to stay open and usable while the picture is open. Basically the picture is supposed to assist the user of how to fill out the section.
I have a User form which had a button which redirects me to a certain sheet, e.g. If I wanted to go to stock sheet I would click cmdViewStock and it would open the stock worksheet meaning it works, but the userform still stays open. Is there a way in which once I click the cmdViewStock it opens the Stock worksheet but closes the userform automatically straight after?
Here is the current code I have for the button:
Code: Private Sub cmdViewStock_Click() Sheets("Stock").Select Sheets("Stock").Range("B4").Select End Sub
When my worksheet is activated, code is executed which defaults the values in my userform controls. The users enter the appropriate data, and with the clicking of a command button, the data is entered into the worksheet. When that is complete, the idea is to return the user to same form, in a default value so they can enter in new data for submission. This will continue until the user selects the Exit button onthe userform.
This is resulting in an 'Object doesn't support this property or method.'
I'm not sure about the appropriateness here of the 'Application.Enableevents=False" here. Since the fields of the userform have change events associated with them, I need to disable that what the userform activation code defaults them.
I have a User Form that I am trying to open with a button click. I made a copy of one workbook with a new name. The Buttons and Macros were all copied as well. I modified the old Userform so that I can release Version 2 of the Userform, and now when I click on the button in Excel, I get the following error : Object doesn't support this property or method. Run-Time error '438'.
The code assigned to that button is as follows:
Sub Button121_Click() ' ' Button121_Click Macro ' Macro recorded 8/11/2002 by Kale Mayfield
What am I doing wrong? HELP! Once you have the Userform populated with ComboBoxes,Textboxes, etc., is there now way of making changes to that Userform? If you make changes and rearrange the locations of the buttons and boxes and labels... does that mess up the button in Excel that opens the form to begin with?
I used the commandBar to create a right-click menu on a userForm but unfortunately I get an error 400 every time I click on the option "Edit" and I don't understand what I did wrong.
Sub CreateCmdBar() Dim st As CommandBar 'delete the pop-up if it exists On Error Resume Next Application.CommandBars("flexgrid_rc").Delete 'Disables enabled error handler in the current procedure and resets it to Nothing. On Error GoTo 0 On Error Goto 0 Set st = CommandBars.Add( Name:="flexgrid_rc", Position:=msoBarPopup, Temporary:=False) 'add two menu items to the new commandbar With st..................
I have a userform that has a listbox that contains data in 7 columns. I also have a command button that when clicked allows the user to edit the data in whatever row of the list box is highlighted. This works fine.
I decided to have a double click in the listbox on a row do the same thing. So I simply picked the double click event and called the command button sub. When I try this, it runs the command button sub, but then it runs the userform Activate sub. This is a problem as it does things that I do not want to occur again.
I then decided to put the command buttom code in a separate sub and then call that sub on the double click -- same result.
Then I just put the exact code that I have in the command button into the double click event -- still does it.
Anyone know why this happens, I see no reason for it. It works great in the command button, but with double-click it run the userform Activate sub when it is done.
Here is the code that runs (if called by a command button it works fine, if called by a double_click in the listbox, it runs the userform_activate sub):
I've found the code below and tweaked to suit my needs but having trouble. Had it working one time. The code below calls a userform when the user double clicks on a cell. The form pops up and is not active/enabled. I cannot select anything on the form until I select a cell on the sheet, then everything on form becomes active/enabled.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'check cells for desired format to trigger the frmSel_WBS.show routine 'otherwise exit the sub Dim NumberFormat, DF NumberFormat = Array("[Blue]General") For Each DF In NumberFormat If DF = Target.NumberFormat Then frmSel_WBS.Show vbModeless End If Next End Sub
I have a spread sheet that has rows of information and once you click on the tally form button (top Left) you can search on the info and it will display in the list box once you click on a result. I have attached the spread sheet, with only 1 row as obviously I dont want to share work data on here.
My question is that, if I want to display a row in the form, I would like to be able to click on a certain cell (Incident # column), it would then recognise the row and display that row in the form.
At the moment I have a EVENT that will highlight a row if a cell is selected. Just thought I'd mention that in case it would interfere.
VB: Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub ' If more than 1 cell is selected, then don't run the rest of the code Application.ScreenUpdating = False
following on from previous post (http://www.excelforum.com/excel-prog...g-up-form.html) what if the selected cell is a merged cell (cell count is greateer than 1), what is the best solution to enable double click event on merged cell ?
Say a user inputs information into the user form, when they click enter, it enters all the data on the next available line in a workbook.
Is is possible for the user to click a cell on a previously entered row, and have the userform populate with the existing information?
example. say the user has to fill out 3 separate areas. 1, 2, 3. However the user only has data for 1 and 2. They go ahead and enter the infromation for 1 and 2 and click submit to transfer to sheet. Now later he gets information for area 3, can the row the information that he previously entered, lets say column 1, row 1, and it reopens the userform with the information for 1 and 2 prefilled from what he entered previously?
i've got a form that shows employee data in labels, and i'd like the user to be able to double click a label to change it (using a pop-up form). is it possible to get the pop-up to show after a double click instead of a single click?
I have a worksheet in whichs Column A Cells , there is corresponding string content in Col. Z. Some cells of column Z contain of many lines (up to 100 line) which i want to reduce to one. I want to select one line through double clicking in the listbox of the userform and all other lines should be deleted and only the clicked one should remain. Code should then jump to the next non empty Z cell.
Columns B, C, D, E, F, G and H should also be shown in the userform and they should be live editable. I have attached and example file with userform and example data.
I have a userform [AddEditMove] with a bunch of textboxes (something like 30) that create entries in a spreadsheet ["Move Records"] along one row. In addition to creating those basic entries, the textboxes also fill in other spreadsheets after manipulating the entered data in various ways.
Therefore, if an entry needs to be changed, it needs to be changed from the userform rather than just on the main sheet, or those other actions won't be taken. At least, that's how I see it now. I'm interested in knowing how other people have handled similar set ups.
Right now, what I think I'd like is for the user to be able to double click any entry on the main sheet and have that action call up the userform and autofill the data from the sheet so the user can change what they need to change and then update it. It would also require a tweak in the userform code -- "if called from cell click, then fill info in selected row, rather than next empty line" -- if that's possible.
I have spreadsheet with client names. I'd like to build logic that will open a form with client information when a user double-clicks on the client name. But I have a couple of problems.
- First, I'd like to protect the sheet and hide the formulas. But when I protect the sheet, I get the "the cell or chart you are trying to change is protected" error when I double click the protected cell.
- If I deselect the "Select Locked Cells" options when I protect the sheet, I no longer have the ability to double-click on the cell.
how I can double click on a cell yet not allow the user to edit the cell (or see the formula)?