I have bug with the code below
rstClient.Open "select * from clients" & "where category=cboCategory.value" And "postcode = cboPostCode.Value",strConnectionString, adOpenStatic
fyi:if i select * from clients without where, all will run good. so i think it is only this part problem.
cboCategory is the combobox which is used to select category, and the same for postcode
My question is - for a Ref range in the user form, I want to set it up to only be able select from column A B and C. ( it has select ABC column at the same time, if missing any column, error message will come out, or select out of this range, the error message should also come up. )
I know how to set the range to all three columns, but I know don't how to ask for " must select from all three column?"
I have a report and users will need to key in password while opening. Currently, i am using inputbox and it works exactly like what i expected. However, the only downside is that i cannot mask the password.
I am aware that I have to use userform to do so. How to replace the input box with userform and still deliver the same result.
I know how to create a user from with title, text box, OK and Cancel buttons, and that's it. How to put it in my current code.
Below are the codes that I currently have. There are 4 possible outputs.
[Code].....
I have a user form containing a drop down list and two text boxes. The drop down list contains at the moment 2 items (address 1 and address 2) I have two worksheets,1st named address 1 and 2nd address 2. So far I have it set up so that the information enter in to text box 1 and 2 plus whichever one of the address is selected from the drop down list is entered in to the next available blank row on the active work sheet. What I would like to achieve is when address 1 is selected from the drop down list all of information is added to sheet 1 in the next blank row, same for address 2, this would be enter into sheet.2
All of the above would run from a command button (Enter)
When my userform is displayed, I would like to have the input field preselected. It is the same as using the Shift-Home key.
I current ly use a shortcut ( Ctrl-M ) to select the sheet and display the userform, as in
Sheets("Select").Activate
Call Worksheets("Select").CommandButton1_Click
UserForm1.TextBox1.Select ' NOT correct
The last line does not work, but it was my stab at the problem
I am trying to get my Userform to open the Insert Picture Dialog
Private Sub CommandButton1_Click()
at a set Path
fPath = ThisWorkbook.Path & "Pics" & "*.jpg"
where the user selects the Picture to LoadPicture in Image1 control. No matter what I try I keep getting errors.
I have an appoinment calendar that is one month per worksheet
- Column headings 09:00 - 20:00
- Row headings 'days and dates of the month' (split into two merged cells of two)
****************See attachment 'Calendar Format.jpg'****************
I have then created a UserForm which has two dropdown combo boxes
****************See attachment 'UserForm.jpg'****************
What I want to do is have a way of using the dropdown boxes to select a column and row reference to enter the other data into the four lines in the outlined box area (see Calendar Format.jpg)
I want to be able to select a date - and to have only the dates from the sheet showing would be a bonus! - and have the script use that as its row reference, followed by selecting the time and having it add the column reference, and so selecting the correct cell to be able to then add all the other pieces of data.
I would just set it up so that it all worked by selecting the first cell, then adding, but I'm worried about other users not doing that and so causing things to be misplaced or overwritten!
I'm more than willing to change most things, but I do need to have those four pieces of info, and I only need to have a simple calendar.
If anything is not clear, please ask and I'll try to clarify further!
Forgot to add - In the pics I'm using 2007, but I want to be able to run this on 2000/2003 as that's what's in the offices and it's going to be shared on the server.
I have a sheet that inserts data into the next blank row. the offset 4 value is a date. Can i have it so when i click the date on this sheet it shows the userform.
what i cant get is how to do it since i dont know what row the date will be in.
user form that has a list box that only picks up visual worksheet (not hidden ones) and then allows the user to select which ones to print via check boxes.
View 5 Replies View RelatedI 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?
I want to be able to select and edit a range of data on a userform. I am trying to pull the information that matches a cell range (Named as Action) into the respective textboxes. But I have fallen at the first hurdle and can't even get that working.
View 2 Replies View RelatedI am trying to sum a dynamic range of values, clear the range (ie. clear the numbers on the spreadsheet), then place the sum of all the numbers in the last cell in the user selected range. (essentially adding all values selected, placing final sum in the last row of the range, and leaving no trace of performing the routine)
I want the range to be selected during the userform running (using showmodal = false). I can sum the selection and write the value to a cell, but I can NOT write the cell to the last value in the selection.
The issue I am having is that the code fails if there is ever a "sheet2" left open. I need it to always target the sheet Data5m.
What seems to happen is the sheet targets the first available sheet. This book only consists of 1 sheet, but generates other sheets later, sometimes extra sheets are left open.
Microsoft Excel Objects - This Workbook code
'The following is for the Userform DataReport code
DataReport.ComboBox1.Clear
Columns("N:N").Select
Range("A2:HX29921").Sort Key1:=Range("N2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Data5m.Range("A1").AutoFilter
Call FillCombobox(Data5m.Range("N2", Data5m.Cells(Rows.Count, "N").End(xlUp)), DataReport.ComboBox1)
DataReport.Show
End Sub
Forms - DataReport
Code:
Private Sub ComboBox1_Change() 'was A, now N
DataReport.ComboBox2.Clear
If Data5m.FilterMode = True Then: Data5m.ShowAllData
Data5m.Range("A1").AutoFilter field:=14, Criteria1:="=" & DataReport.ComboBox1.Value
Call FillCombobox(Data5m.Range("X2", Data5m.Cells(Rows.Count, "X").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox2)
[Code] ..........
I have a userform that allows you to select months to apply to an autofilter, In the code below ComboBox2 is the beginning Month, and Combobox5 is the last month in what I want to be a range of months. I need it to account for the months inbetween. Currently using my code it only selects the 2 months, not those inbetween.
Code:
Private Sub RunButton_Click()
Unload DataReport
Rows("1").AutoFilter Field:=24, Criteria1:=Me.ComboBox2, Operator:=xlOr, Criteria2:=Me.ComboBox5
Call Filtered
Call AMasterBuild
End Sub
I current have code that fills in textboxes in a userform with values in cells:
Code:
Private Sub CODE_Change()
Dim ws As Worksheet
Dim fm As Worksheet
Set ws = Worksheets(ActiveSheet.Name)
Set fm = Worksheets("Form")
[Code]...
How to I modify the lines under Checkbox1.Enabled = False so that they only take a section (of varying size) of the text in a cell and put it in textboxes in a userform?
ie. The text in a cell is "Outer: A123 ; Inner: B456." I would like A123 to appear in a textbox called OuterColor and B456 to appear in textbox called InnerColor
How do I allow the user to select from a dropdown list but disallow them from entering value into the textbox?
View 1 Replies View RelatedI have a userform with 5 listboxes on the first page, each with multiple options. They are all set up so any number of options can be chosen.
2 are populated by the 'add item' method, and the other 3 pull data from 3 columns of the database when it's initialised to ensure all possible options are included.
My question is this - as there may be many options in each listbox to scroll through, if someone wanted to search on all the options in the listbox, it would be easier to have a 'Select All' option at the top, but I have no idea if this is possible, let alone how to do this -
Why is the below code not valid? I am using a userform with a cancel button and i cant it to copy and paste some lines on sheet Chart_Data as it unloads. But i just get the error
"Runtime Error '1004': Method 'Select' of object '_Worksheet' Failed"
Private Sub Cancel_Click()
Chart_Data.Select
Chart_Data.Rows("6:7").Copy
Chart_Data. Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Unload Me 'Cancels Operation
Exit Sub
End Sub
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 7 Replies View RelatedThe macro below in its current state adds data entered from the userform to a specific sheet. I would like to change it so that a player can be selected from combobox named txtmplayer and the data entered be copied to that specific players sheet.
If this information is vital: There are 8 players. Player sheets can be named player1, player2, and so on. I would like to keep same method for entry (finds first available row)
I have very limited VBA skills and I have tried to get a vlookup working on a userform. I need data from a range selected in ComboBox1 to drop into Textbox1. I have tried but it doesn't work.
I also need if possible a button that pushes the result of TextBox1 to a cell and another button that allows the user to edit the TextBox1 value in situ.
So I'm creating a UserForm that takes user input to create a worksheet based on the values input. My question is: How to select a range of cells based on a variable input value? For example, it asks how many competitors there are, and the user can input 5, 8, 10, etc. So I want that many cells in Row 1 selected, whatever the value input is.
View 9 Replies View RelatedI have a userform which allows user to selected a date formt he calendar. In my code I name the value selected myDate. I want to have a line of code at the begginning of my code that checks if myDate = a date already listed in column H on a sheet named "all dor's". if it does match then a msgbox appear saying "date already entered" and the code return to the opened userform.
View 9 Replies View RelatedI have a UserForm that I want to use as a “Print Selection Page” using check boxes to make selection or multiple selections of pages to print from different spreadsheets but same work book.
View 9 Replies View RelatedI have made a simple procedure (which will extend to make something that I have to make but for time being) it asks user to select a chart type in a userform using option buttons and then the chosen chart type is taken as the chart type and makes the chart.
problem is that the typechart - variable to identify the chosen chart type - is not being recognised. Here's the coding, the variable is not passed (in yellow bolded) from one procedure to another?
Private Sub NextButton_Click()
Call ShowType
If bFlag = False Then
MsgBox "Not Selected"
Call ShowType
Else
Call MakeChart
End If
End Sub
A custom UserForm pops up when a cell is double-clicked. The form contains a ListBox that presents the user with a list of values to select. When the UserForm first pops up I want the item that matches the value of the activecell to be highlighted/selected.
View 8 Replies View RelatedI wanted to create a userform with multipage. On the first page I want to use a ComboBox that would allow the user to select the customer. The customer name is on the sheet that I named "Customer" which also contains the street address/city/state/zip. What I would like is, once a customer is selected, I want a textbox that will be populated with the correct address taken from the sheet named "customer".
On the second page I'd like to use it for Product Info. I want to have a ComboBox, Text Box, a Command Buttons. The ComboBox is to select the "Model#" from the sheet named Product Info" and the TextBox is for the user to write their comments. One of the Command Button is to be view a sheet named "Quote" and the other is to send the "Quote" through email.
I am working with DatePicker calendar. I have two userform with date entries. In one userform I will have 2 textbox to enter dates. In the other one I will have 4 dates to enter. I used to have Excel 2007 with Calendar Control and used to work fine but had to make multiple calendar to control individual Textbox in multiple Userforms.
I am looking for a way to, If a userform is selected to trigger only the textbox name that is within that userform. If I add more entries the code adds automatically the date from the previous userform in the next one.
[Code].....
I was thinking that this would work but it still adds the date to my frmdashboard userform if I am in my frmdata userform.
Is there a way to trigger only the userform that I am currently in?
Using Excel 2013.
Note: I also have a "Clear All" VB Code that will delete every entry in my userform on Initiate but I am looking to not go this path.
Is there any way to set the list from a Combo Box to automatically start in the middle?
Whenever I select a Combo Box, the drop down list always automatically starts at the top of my selected range, ideally I would like it to start in the middle.
For example my Range that I am filling the Combo Box with lists dates: Today()-7 to Today()+7.
At the moment the first item in the combo box list is Today()-7, I would like it to be Today().
Arised from my earlier posting in Populate ComboBox With Specific Sheet Column Range. I have the following working code below, but am having trouble finding coding examples to select specific cells from the selected row (that was found by selecting a ComboBox value)and update TextBoxes with those individual values after the UserForm has been initialized (the bold "GREEN" comment in the code below). I have been able to find plenty of references to update TextBox values to Cells, but that doesn't do me much good in this application since the User needs to verify the old data in these cells before updating them using the UserForm TextBoxes.
I was toying around with several different variations of code (none of which worked properly), so I left it out for clarity of my working code. I'll post up this non-working code as needed, because I really wanted a fresh answer...not what I was trying to do. The attached file should be sufficient to see what's going on
Private Sub UserForm_Initialize()
With Sheets("SR Information")
.Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Name = "MyRange"
End With
SRnumber.RowSource = "MyRange"
End Sub
Private Sub SRnumber_Change()
Dim ServiceRequestNumber As String
Dim c As Range
Dim rngG As Range
Sheets("SR Information").Select
With Selection
ServiceRequestNumber = SRnumber.Value
For Each c In Intersect(ActiveSheet.UsedRange, Columns("a"))
If c = ServiceRequestNumber Then..................