I am creating a userform, and in this form I have two text boxes and four radio buttons. For one of the text boxes, the user is to insert a number. I have to make sure this number is not already in a range in the workseet. How can I make the userform not accept numbers in a text box that are already in the range in the worksheet?
View 2 RepliesI have a simple user form with 8 frames each containing four option buttons. I need a routine to validate that a each frame has had a single option button selected once a command button is clicked.
Here is the code I'm currently using but it isn't working correctlt.
Private Sub CommandButton2_Click()
Dim Cnt As Integer
Dim Ctrl As MSForms.Control
Dim WS As Worksheet
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "OptionButton" Then
If Ctrl.Value = False Then
MsgBox "Please answer ALL questions"
Exit Sub
End If
Else
MsgBox "Thankyou for completing the questionnaire"
Unload Me
Exit Sub
End If
Next Ctrl
End Sub
I have a Userform that has 4 Textboxes (named TextBox1, TextBox2, TextBox3 and Textbox4) and 7 Option Buttons (named OptionButton1, OptionButton2, OptionButton3, OptionButton4, OptionButton5, OptionButton6 and OptionButton7). I need to validate that the following criteria is met:
TextBox1.Value Does not = ""
TextBox2.Value Does not = ""
TextBox3.Value Does not = ""
TextBox4.Value Does not = ""
One Option Button is selected. (It can be any one of them 1-7, Just one needs to be selected).
Is there an easy way to check this data before executing a sub procedure to move the data?? Here is my try at it:
Code:
Sub CheckData()
'Is this the correct way to check data ?
If TextBox1.Value = "" Then
TextBox1.SetFocus
MsgBox "Please enter a Batch Date", vbCritical
[Code] .......
I am in the process of creating a module to validate a MulitPage UserForm that is composed of, among other controls, multiple frames, each containing a TextBox and two OptionButtons. I need to identify those frames where the TextBox value is not null and neither of the OptionButtons have been selected. The module will pass as variables the TextBox value, OptionButton captions and Frame caption to another module that will create a UserForm with the invalid Frame/TextBox/OptionButtons sets for the user to correct.
The interim code listed below runs as a command button on a test UserForm. The MsgBox displays only the first of the OptionButton’s caption as Opt1 and Opt2. Can anyone tell me why and where I went awry?
Public WithEvents As MSforms.TextBox, Opt1 As String, Opt2 As String, vtxt As Long, vFrame As String, vTag As String
Private Sub CommandButton1_Click()
Dim pPage As Page, cCont As Control, vCont As MSforms.Control
'Dim Opt1 As String, Opt2 As String, vtxt As Long, vFrame As String, vTag As String
Is there a more elegant way of accomplishing this? (Control Tags are already spoken for.)
Using Excel2010 in Windows7. I have a UserForm
1: TextBox for Input Username (txtUsernameIn)
2: TextBox for Input Password (txtPasswordIn)
3) Submit Button (cmdSubmit1)
I have a Worksheet
ColA: Username
ColB: Password
And this entire Range A:B, I have named "UserRegister"
I have code, attached to the cmdSubmit1 button on my UserForm. The purpose is to allow access to a veryhidden Worksheet "ADMIN".
The code 'works' if I enter in both the correct UserName and Password (ie: the code completes & Admin sheet opens up).
The code 'works' if I enter in the correct UserName and the wrong password (ie: code completes it's run, my msg pops up to say there is wrong input, unloads the form & sheet ADMIN stays veryhidden).
However, if I enter the wrong username (with either right or wrong password) the code breaks and I get a debug error. I know my code is obviously wrong/missing something etc - but I can't work it out! The line of code highlighted on the break, is between the *** below
VB:
Private Sub cmdSubmit1_Click()
Dim Username As String
Username = txtUserNameIn.Text
Dim password As String
password = txtPasswordIn.Text
[Code] ......
Can anyone help me to get a validation to check that what is entered in a cell, lets say A1,
has to be number between 100000 and 899999 (this I already have) OR a value/text from I100000 to I899999. So it only acceots a vlaue betwwen 100000 and 899999 and it is also ok to have the letter I at the begining.
I would like to use VBA to validate the data in an Excel worksheet against either a named range or an array of valid data.
The valid data comes from an accounting system. It is a list of customer numbers. I am using ODBC and SQL to get the data into an array and then into a named range. This is working fine.
I want to validate the customer number column in my source Excel worksheet against either the array or the named range.
There are a variable number of lines in the source data.
I have a VBA procedure which starts at cell A2 and then works down column A until it reaches a blank cell.
I would like to use a VBA formulae which does something like:
IF active cell offset (0,3).Value in (named range or array)
Then set cell colour for active cell offset (0,3) = Green
Else set cell colour for active cell offset (0,3) = Red
End If
Or something like that.
Can this be done using VBA? Or do I need to create a new column in my source data and use VBA to place a VLOOKUP based on a named range into this new column?
I have a VERY complex custom data validation formula that is getting crazy. I thought it might be easier to use a user defined VBA function to handle it, but was unsuccessful.
I was able to write a VBA User Defined Function & use it within my spreadsheet to derive the value of another cell, as follows: =IF(OR(ISBLANK(B12),myValidation(B12)),"OK","NG")
I tried using it as a Custom Data Validation on the cell itself (=myValidation(B12)), and I get an error: "a named range you specified cannot be found".
Can the function be used in this way or could someone suggest another way of handling this?
I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFind As Range
'Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
Set rngFind = Range("rngVal"). Find(Target.Value)
'If data in column C changes, do the following
If Target.Column = 3 Then
'If the value is found on the validation list, do nothing.
If rngFind Is Nothing Then
Else
MsgBox "You must enter one of the following in this cell:"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
Else
End If
End Sub
I would like to " Validate Data In A Vertical Column To Not Allow Non Consecutive Numbers Less Than 100"
756415
10
456132
7
456123
12
The above is a valid list. Below would be an invalid list:
756415
10
13
456132
7
7
456123
12
13
This part of a larger scope, but I have a macro that will crash if the data entered is entered by way of the invalid list, it works perfectly with the valid list. I am limited to one column user's will input the data via a Barcode scanner that after it recieves input it enter's a "Hard" return. This is a warehouse pickticket program, user's scan their ticket id's (numbers greater than 100000) and then the number of lines on the ticket (usually not greater than 15)
I am trying to create a command button on a userform that when pressed will only display a specific worksheet of an excel workbook. I have the userform setup with the command buttons that will point to a specific worksheet but it is still behind the userform.
How can i make it so that when i hit the command button in the userform then print preview is shown for that worksheet, and the other worksheets are hidden. I also want to make it so that the workbook is hidden and all you see is the userform until you select a sheet.
I have a worksheet with following values:
A
B
C
1
Shorts
75
[Code]...
Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".
What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.
Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.
I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.
I need help to this : When i type a number to a cell and press enter , i want to check if this number exist in a range of cells (in sheet 2) , and if exists , excel show me a message. Actually i use it for my *** club. Number is the client code. When i writte 50 in a cell , i need from excel to check if this client own me money , and show me some message..
View 8 Replies View RelatedHow would I go about counting the number of times TA shows within strings of text in a range within a sheet. Example: TA,MH in cell A2, CB,TA in cell C40, ES,TA in cell Q19. Result would be 3. Ideally, I'd like the formula to reference a cell that has TA as the look up data such as in A1 I'd have TA.
View 4 Replies View RelatedI need help with creating a macro that runs when a user enters a value in the 'Numbers' column, copies and pastes data in the corresponding worksheet 'Worksheet' column by the value of 'Numbers' column data. An excel file is attached.
View 6 Replies View RelatedThis 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 some experience with excel, but until now have not ventured into VBA and macros.
I have a workbook which will have the following sheets:
1.Absence Summary sheet - Summarises data from each employee's individual sheet.
2. Template Sheet - A sheet formatted as an absence record sheet, but without data.
3. Individual employee Absence record sheets - Based on the Template sheet.
I have read with interest the various posts and help files on User Forms & Macros, but have got a bit stuck.
My Aim: ....
In the attached worksheet I have UserForm2. When I click on open compare form button on the menu sheet it opens UserForm2, I would like the information I select in the first 7 combo boxes Vegetable - Ball on UserForm2 to loop through the data in the database sheet Columns A:G and compare the entries to the non empty/not blank cells in each row. If the form contain data that matches all the non empty/not blank cells in a row in the database sheet then it is a match and should show the label and display the message. If the form entries does not match to the non-empty/not blank cells in any of the rows on the database sheet then do nothing.
The problem I am having is getting it to loop through the sheet and bring back the right results. It is only matching on row 2 of the database sheet when I select cabbage in the vegetable combo box and apples in the fruit combo box . I cannot figure out how to get it to loop through all the rows for the range I want to compare (A2:G7) - I need this range to be flexible so as data is added it will expand to read all added rows.
The code is on the btnSave_Click() for UserForm2
I attached the spreadsheet and I am explaining what I want to do and the expected result.
Fruit
Fruit Type
Vegetable
Games
Toys
Cereal
Ball
[Code] .....
What I want to do is loop through the Database sheet and if the fields on the form contain all the values in any row of the Database sheet, excluding empty cells in the Database sheet, then display a message.
So if on the form I selected Broccoli fron the vegetable combo box, Cricket from the games combo box, puzzles from the toy combo box, bananna from the fruit combo box, grits from the cereal combobox, and baseball from the ball combo box, in the databse sheet tabel shown above the match would be row 6 since the values for vegetable, game, toy, fruit, cereal and ball on the form matches what is on row 6 of the Database sheet. It does not matter what other fieds are selected /filled in on the form, the match should only take into consideration the populated cell in each row of the database sheet.
So, if the user enters Apples in the fruit combo box and Cabbage in the vegetable combo box but had blank or something other than bike in the toy combo box on form it would be a match to the Database sheet row 2, regardless of what the user enters in the remaining fields on the form
If the user enters Berries in the fruit combo box, Blueberry in the Fruit Type Combo box, Carrot in the vegetable combo box, and Grits in the cereal combo box it would be a match to Database sheet row 3, regardless of what the user enters in the remaining field on the form .
If the user enters Apples in the fruit combo box, Cabbage in the vegetable combo box, and Bike in the toy combo box on form itwould be a match to the Database sheet row 5, regardless of what the user enters in the remaining field on the form .
If the user enters Grape in the fruit combo box, Carrot in the Vegetable combo box, Cards in the game combo box, and football in the ball combo box on the form it would be a match to Database sheet row 7, regardless of what the user enters in the remaining field on the form.
If the user enters Kiwi in the fruit combo box, Cabbage in the vegetable combo box, and Bike in the toy combo box on form it would NOT be a match to the Database sheet because the Database sheet does not have a row that contain Kiwi, Cabbage, and Bike.
So basically, if the entries on the form match the exact values for all the non-empty (blank) fields for any row in the Database sheet, then it is a match.
-If the entries on the form do not contain an exact match to all the non-empty (blank) fields for any of the rows in the Database sheet, then it is not a match.
-If it is a match show the label and display the message box
-If it is not a match the do nothing
I have a list box that I'm using to pull customer information from my "Customer" sheet to my "Invoice" sheet. I can actually open, select, and close the userform. However, I can't get the macro to put the information into the specified location on my "Invoice" sheet.
Here's my code so far:
Private Sub Cancel_Click()
Application.ScreenUpdating = False
Unload Me
End Sub
Private Sub customer_Change()
End Sub
Here is the "Customer" sheet I'm pulling the info from:
And here is the location on the "Invoice" sheet that I'm trying to direct the userform to:
And here's my userform just for reference:
The userform works perfectly until I press the OK button. That's when I get the error.
I have included the table. What I want to do is in a user form pick a county ie Devon, Then by clicking "go" all entries that contain "Devon" are then copied to a " sheet 2" I don't want to use the Ctrl-F
View 2 Replies View RelatedI've got a User Form that works perfectly. It's activated on Chart Month sheet, you enter data into it and it switches to Comments sheet and copies the data, before returning back to the Chart Month sheet.
I have now added the same functionality to Chart Week sheet. Both Chart sheets use the same comments data, so it doesn't matter if you run the User Form from Chart Month or Chart Week. However, when I run the User Form from Chart Week then it switches to the Chart Month sheet once it copies the data, as I have Chart Month in the code. How do I get it to return to the sheet that it was originally on ie either Chart Week or Chart Month?
Code:
Private Sub CommandButton1_Click()
Dim emptyRow As Long
'Make Sheet30 Active
Sheets("Comments").Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("D:D")) + 1
[Code] .........
i have a main.xls file and two data file dat1.xls and dat2.xls
mail named file have ar - br- cr- dr- er -fr sheets
dat1 named file have ar-br-cr sheets
dat2 named file have dr-er-fr sheets
and all this files data source is
colomn source a - fd
row source 29-4000
i want to make two commandbutton to main file first for dat1 second for dat2 file and i need a code to use at this buttons to make
when dat1 and dat2 close
main file user when click first button
copy dat1 file ar sheet colomn source a - fd row source 29-4000 cells to main file ar sheet colomn source a - fd row source 29-4000 cells
copy dat1 file br sheet colomn source a - fd row source 29-4000 cells to main file br sheet colomn source a - fd row source 29-4000 cells
copy dat1 file cr sheet colomn source a - fd row source 29-4000 cells to main file cr sheet colomn source a - fd row source 29-4000 cells
I have to SET sh1, then activate sh1 before I can SET rng1. Is there a way to just...
Set rng1 = sh1.Range(Columns(1), Columns(11)) ' why doesn't this work?
Sub Raw_Sales()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rng1 As Range
[Code]...
I have a userform in my workbook with a button to access it on twenty different sheets in the workbook. Currently the userform will only populate the sheet titled "Blank1". How can I change it so it will populate the active sheet without naming it? So no matter what sheet I'm on when I hit the submit button the userform will populate only the sheet I selected the button on? See macro below.
Code:
Private Sub OKButton_Click()
Dim NextRow As Long
Sheets("Blank1").Activate
' Unprotect Sheet
ActiveSheet.Unprotect
[Code] ..........
Hi. Does anyone know a formula to copy a selected range of cells on sheet one to a range on sheet three when a check box in checked. Ex. copy range a4:j4 on sheet one into a4:j4 on sheet three once the check box for on sheet one is checked?
View 9 Replies View RelatedI can’t seem to add the “Insert New row” or where to add or maybe I’m not even doing it right. I have a worksheet with 4 column and need to only function in these columns as I have information doing something else.
In Column A, B, C and D are my main column for the following functions:
Column A will act as titles
Column B will act as sub titles (There can be multiple sub titles withing the same title, needs to be added on an as when requirement)
Column C will act as possition (There can be multiple position withing the same sub Title, needs to be added on an as when requirement)
Column D will act as sub position (There can be multiple sub position withing the same Position, needs to be added on an as when requirement)
All columns will react to what is in Column A. I have the following code for column A:
[Code] .....
This will look in my next available row to see if it is a blank row then add my information from my userform listbox.
I have tried to add the same procedure to my column B but don’t even come close to what I need in this function.
I need that when I double click my sheets cell in accordance to the following code:
[Code] ....
...to show my userform then to add the information but only to look in Column A to D and not pass this column.
I have added a Workbook to add the other information has it could get long to actually explain but to quickly explain:
I will need to add a title (already set up with a userform, listbox and on click of this listbox it will transfer the information to my sheet) This will look in my Column A to see where is my next available blank row and add the listbox value to it. If I double click column B, next to the columns A title, I need to add this information on the next available blank row but to create a Insert new row as there could be addition information going in column A or B and so on... I presume it will be the same code for C and D as this has to function the same way.
See Attachment for details and information : Book1.xlsm
First off I have an excel sheet that I have split into two windows. excel sheet.jpg
I am looking for a formula that will change the bottom sheet number a color if it exists on the top sheet.
I'm trying to create a formula that takes from "Manual" Sheet the number from colume G2 and multiply it by the percentage in sheet "AllocationRule".
My formula currently is =Manual!$G$2*AllocationRule!$B5.
What needs to happen is that the total number in "Manual" needs to be distributed evenly in 4 rows by the percentages allocated in "AllocationRule".
Right now I can't copy my formula over to the sheet because the "AllocationRule" should stop at B5 and not go further and the G2 from "Manual" should not change for the percentage allocation but should change to the next row for the next month.
And then after I've done the calculation I want the LOB in "AllocationRule" to be displayed in the LOB in "H1913_H1914" but I'm not sure what formula to use.
Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
In a sheet (4) I have a pivot refreshed by a VBA macro code. Since I have defined a Chart on this pivot, I need to copy this pivot in a new sheet to avoid to reduce rows using a filter of the Chart. I copy this pivot using this VBA code
Sheet4.Select
Sheet4.Copy After:=Sheet2
I have e new sheet with a number Sheet(n) not equal to maximum sheet number +1
For a new run of the VBA macro I need to delete this sheet
Sheets(n).Select where (n) is not = maximum sheet number +1
ActiveWindow.SelectedSheets.Delete
Then I need to avoid the message box where I need to confirm to delete the sheet because the sheet is not empty.
Is there a solution to copy a pivot in a new sheet replacing the previous pivot and vithout changing the number of the sheet?