I have two check boxes in a worksheet created from the "Forms" toolbar.
Is it possible to prevent a user from clicking these two check boxes if he meet a condition in cell A1.
Eg, If cell A1 value is "Marketing", the user is not able to click on these two boxes.
Is there a method by which I can prevent a user from renaming a worksheet by doubleclicking on the sheet tab ? Please note that I do not wish to use workbook protect method.
View 9 Replies View RelatedI'm using code based on the following post: Add Borders To Cells Of Spreadsheet From Access. That code was developed for a relatively small recordest. I am using the same coding style to transfer a recordset that has roughly 2000 records in it. Since this is all taking place over a network there is a period of time (about 15-20 seconds) where Excel is open and the user is watching the cells get populated from Access. If, during that period of time when Excel is open, the user clicks in a cell during the population of data, it stops the process and causes an error on the Access end (Error 50290: Application-defined or object-defined error). Is there a way I can prevent user input (perhaps prevent signals from the keyboard and mouse) until after the data has been transferred? I know this is a long way to do a TransferSpreadsheet command but I like the way the column headers get formatted as well as the column sizes. I also think it's pretty cool to watch the data populate as it goes.
View 2 Replies View RelatedI have a list dropdown from cells A2 to A10. If only when a user selects "orange", he has to key in some description in column B.
Eg if he selects orange in A2, he has to key in some description in B2.
Is it possible to set some formula or macro which prevents user to save this file if he leave blank in B2 or B10, depending on which row he has select "orange"?
I am adding up the value of a range of cells in one cell. What I want is If the value of the cell which is adding up the values of the range of cells exceeds a particular number it should throw an error.
View 6 Replies View RelatedI want this statement translated into a macro.
Do not save file year.xlms if total of column N in sheetR does not match with total of column O in that sheet. I also want a msgbox if this happens.
what I want. It relies on an input box asking the user to enter a part code, which when found creates a duplicate copy. However, if the user enters a part that doesn't exist I get a run-time error. Can anybody point me in the right direction so when a user enters an incorrect part they get a message telling them it doesn't exist.
Private Sub SortTest_Click()
Dim s As String
Dim r As Excel. Range
Range("A2").Activate
s = InputBox("Enter the number you wish to find")
If StrPtr(s) = 0 Then
MsgBox "You must enter an existing part number!"
Else
Set r = Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Copy
Sheets("APL").Cells(r.Row, "A").Insert Shift:=xlDown
Application.CutCopyMode = False
Application.Goto Sheets("APL").Cells(r.Row, "H")
Selection.Offset(-1, -5).ClearContents
Selection.Offset(-1, 0).Select
End If
End Sub
Split from Copy Rows, Meeting Criteria, From Multiple Worksheets & Append To Summary Sheet
This has been wrecking my head for a week. For the debtors tab it is all of the rows which have the value "Unpaid" for each of the 12 months that need to be copied into the tab. For the invoice I thought there could be a button at the end of each row and when you clicked it, it put the info into the invoice template and then the person could just print it off and resend it. And then do the same for the rest of the invoices.
I'm working with 2 different sheets, sheet1 and sheet2.
I'll type in entry 1 to 4. 2 comboboxes, 2 cell values. then click on the command button1.
i'll pass over the values to sheet2 like this:
array(comboBox1.value, range("a2").value, comboBox2.value, range("a4").value
I was asked to check against the last time the user click on the command button.
if combobox1.value, range("a2").value, comBoBox2.value, range("a4").value MATCHES exactly the last entry in sheet2, it'll give a popup Box before it continues.
Private Sub cmdNext_Click()
' Save form contents before changing rows:
SaveRow
' check that it is not the last row - if it is then show message
' and do not go to next record:
If Sheets("Data"). Cells(lCurrentRow, 1).Value = "" Then
MsgBox "This is the last Output."
lCurrentRow = lCurrentRow - 1
Exit Sub ' Abort if no data:
Else
LoadRow
End If...................
I'm having trouble with is the bit where it is supposed to ask for the txtAddInfo to be filled (if blank) and if the txtdifference is greater than 10% or less than -10%. It seems to work fine if the txtDifference is less than -10% but not if the txtDifference is greater than 10%. It just seems to get stuck, the message box is displayed even if txtAddInfo has infomation within it.
I'm not sure why as I use ..........
I'm attempting to use VB in excel. I've created a form and several check boxes with specific options for each one.
when one box is clicked I want the data from that box (whatever the option is) - to appear in a specific cell (ie: cell 17)
With out using Conditional Formatting, how can I check a cells value if it can fall within a range of +-.002?
I have this code but it seems to have limitations.
Suppose I have a excel sheet,in this sheet has 7 columns(A to G).I need to do with my sheet with this condition.
-Keep the rows that have the same value of datas in column A,C,G and they must have different datas in column E and F.
-The other row that not same as above condition must be deleted.
Is it possible to have excel continually check if a columns used row count in a particular column is greater than a number before moving on to the next part of code?
The reason I'm doing this is because one formula will pull in data from bloomberg which can take some time (5 seconds or so).
Some other code that places formulas into other adjacent columns depends on the number of rows in the first column. So the code following the data pull wil fail if the above hasn't finished pulling yet.
I was thinking about doing something like a do nothing until true?
Here's what I have that is not working:
Do Until Range("A1:A6").Cells.SpecialCells(xlCellTypeConstants).Count > 5 Range("F1").Select
Loop
I expect that the number of used rows in column A should be greater than 5 once data is pulled. In this case I just had VBA select F1 until the count was greater than 5 before moving on. but I think it's interfering with the pull and will coninue to loop..
If you open the attachment.. then you will see that Column A has all the names and Column B has it's Value.. likewise.. Column D has all the names and Column C has it's values...I'm trying to first match the names from Column A to Column D and then see if Column B matches to Column C..
View 8 Replies View RelatedI would like to change the color of a cell to either green, yellow, or red based on a few conditions. One of the conditions would be based on whether a check box was checked or not.
View 2 Replies View RelatedI need to count all unique entries in column A (list of names) (this bit I can do!)...however:
Column B gives each name a value of A or B, I need to count all unique names with an A and all unique entries with a B.
eg
Matt A
Matt A
Matt A
Bob A
Tom B
Tom B
This would give a count of 2 for A's and 1 for B's.
I am trying to write a formula to figure out Body Mass Indexes for certain age groups and whether or not they fall into a High or Low risk category. So, I am trying to write a formula that does the following. I have 3 columns, Gender, Age and BMI. I need the formula to do the following.
IF Gender = M AND Age >18, <39 AND BMI >7%, <19%, Then return an "L" into 4th column
IF Gender = M AND Age >18, <39 AND BMI >19%, Then return an "H" into 4th column
IF Gender = M AND Age >40, <59 AND BMI >14%, <23%, Then return an "L" into 4th column..............................
I have attached excel with the logic I am using, but I have a problem and needed logic to update to make sure it is not inserting same data (duplicate data).
Please update the logic so that if it is having same data in sheet 3 it should not insert or delete duplicate.
Code:
Public Sub CopyData()
Dim ws As Worksheet, bi As Byte, vData(1 To 9), bi1 As Byte, vData1(1 To 9), bi2 As Byte, vData2(1 To 9), bi3 As Byte, vData3(1 To 9), bi4 As Byte, vData4(1 To 9)
Set ws = Sheets("Sheet2")
For bi = 1 To 9
[Code] .........
I wonder if it is possible to make a macro that right clicks a cell and then chooses a option from the list?
View 11 Replies View RelatedI am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies View RelatedI'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts
8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"
these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I am using this code
View 5 Replies View RelatedRunning into this issue of "transworksheet macros"
I have a master "update" commandbutton on sheet 1. I have two sub "update" buttons on sheet 2 and 3 respectively.
What I would like to do is have the user click the master button, that would "click" the two sub buttons so they run their respective macros on their respective worksheets (within the same worksheet.
I have tried pasting the macro code in the master button, telling it to select the sub worksheet and then run the macro, but the marco runs itself on the master worksheet instead.
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...