Show Tick Or Cross Upon Checking Cell
Jun 2, 2008
i don`t know how to make this in VBA
But please allow me to explain, if I have numbers in Cell F9 I want image to be displayed as (X <---- which it means wrong) on G9 and message to be appear in H9 says only words are allowed. In case, cell value are words; I want it to show image <---- which it means right) and the message to be say correct. And if cell is empty I want it to show image (!) and the cell beside it the message should say (Please Fill up).
I want to apply this to words instead of numbers as well.
View 4 Replies
ADVERTISEMENT
Jul 22, 2009
I am trying to use WINGDING FOnts on some forms. I am not having full success with them. Sometimes they seem to work, other times not at all, and then sometimes if the code is less that 127. I am programically creating the forms from a worksheet.
The code used to create is as follows:
Sub MakeUserForm()
Dim TempForm As Object
Dim NewButton As MSForms.commandbutton
Dim NewLabel As MSForms.Label
Dim NewTextBox As MSForms.TextBox
Dim NewOptionButton As MSForms.OptionButton
Dim NewCheckBox As MSForms.CheckBox
Dim x As Integer
Dim Line As Integer
Dim MyScript(4) As String
Dim BC As String
'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
'Create the User Form
With TempForm..............
View 6 Replies
View Related
Jan 10, 2007
I was reading your Tick Cell Upon Selection article and it works great. I'm just wondering if there is a way to modify it so that they can only select one of the cells in a row.
For example, in this spreadsheet, the cells I have marked for "tick upon selection" are N8-Q8. In this worksheet, though, I only want the user to be able to select one of the four options. Is there a way to format the code so that they either cannot select another cell until they have deselected the first one, or the first one unchecks itself and the new click ticks that cell?
Example: Person selects N8. Person then selects P8. Can it not allow P8 to be selected (and give a warning message) or can it uncheck N8 when P8 is checked?
View 6 Replies
View Related
May 9, 2014
So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:
=VLOOKUP(B(//SomeValue),Locations,1,FALSE)
I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:
=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")
Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010
View 2 Replies
View Related
Mar 18, 2009
I received a spreadsheet form containing some checkboxes that I need to tick. The problem is I dont know how can I tick/show the check mark on the checkbox. everytime I double click it, macro vb screen appears.
View 4 Replies
View Related
Jun 24, 2009
I am running some VBA code that works fine, until I specify a filename that contains embedded blanks. Hopefully, the following code and screen captures will demonstrate what is happening. First the code that opens the files ...
View 8 Replies
View Related
Sep 28, 2007
I would like to achieve the affect where if you click on the cell its contents will change. For example, if you click on it once, a checkmark will appear, but if you click on it twice and x and if you click on it a third time a - will be displayed.
I was thinking along the lines of a marco for the spreadsheet, which would run an if loop to check what was currently in the cell then change accordingly. Is there a command that would allow me to do this, or would is there an even easier way? side note how would I even display a checkmark of square root sign, through vba? This is what I have, It was working but now it does nothing and I cant remember what I tinkered with.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 6 Then
If Cells(Target.Row, 6) = "" Or Cells(Target.Row, 6) = "-" Then
Cells(Target.Row, 6).Formula = ""
Application.EnableEvents = True
ElseIf Cells(Target.Row, 6) = "?" Then
Cells(Target.Row, 6).Formula = "x"
Application.EnableEvents = True
Else
Cells(Target.Row, 6).Formula = "-"
Application.EnableEvents = True
End If
End If
Application.EnableEvents = True
End Sub
View 2 Replies
View Related
Nov 13, 2008
I need to click on a cell and when I click on it, it should change colour and insert a tick (or other symbol).
View 14 Replies
View Related
Aug 23, 2007
I have attached a spreadsheet which, when you enter a matching value in the vehicle column, the cell that matches the vehicle name in both instances (column and row) is ticked. Think I am wanting to use a worksheet_calculate function but cannot figure out how to write the appropriate lookup in VBA. I do not want a formula in the cell.
View 9 Replies
View Related
Dec 10, 2008
I am using Jon Peltier's worksheet_change event code to try and automate chart axes.
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$AG$5"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$B$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case "$AG$7"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MajorUnit = Target.Value
Case "$L$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MaximumScale = Target.Value
Case "$N$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MinimumScale = Target.Value
Case "$AH$7"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MajorUnit = Target.Value
Case Else
End Select
End Sub
However, I have some of the cells setup as formulas....but worksheet_change apparently only updates values when manually changed.
View 9 Replies
View Related
Apr 2, 2008
I am trying to use excel to score a test. I want to tick a cell to do so. There is an excellente information about Tick Cell Upon Selection, posted here http://www.ozgrid.com/VBA/excel-checkbox-tick-cell.htm. Its range is limited to (A1:A100)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub
How can I increase the range so it would target C1:C30, E1:E30 ( total of 17 columns)?.
View 3 Replies
View Related
Apr 22, 2014
I have a spread sheet with various tick boxes, that when ticked calculate an accumulative percentage in a cell. This cell is the basis of my graph.
E.g. if the cell displays 80% - the chart with show 80% - simple.
However, I want to write a vba code that changes the fill colour of the chart depending on the percentage.
i.e. if the percentage data = 0-49% I wish the chart to display as red. 50-69% - yellow and 70%+ = green.
View 2 Replies
View Related
Apr 8, 2014
I have a tracking sheet that is used to show where a specific project is within the lifecycle and would like to automatically set a summary value depending on the last data entry within a range of cells in a row and also set it to RAG status depending on the value.....
View 2 Replies
View Related
Oct 9, 2008
I have a spreadsheet with 2 columns of values. I need to check that the two columns have the same values, however the last digit of the values will differ every time, in the first column the value will always be a 3 and in the 2nd column it will always be a 0. Unfortunately the amount of digits before the last, differs from 6 to 9 so its not as easy to do a trim. Example
Column 1 Column 2
11111113 11111110
222222223 222222220
4444444443 4444444440
I need to check that the first digits (no mater how many) are all the same except the last digit.
View 3 Replies
View Related
Nov 28, 2011
Programmatically speaking, any way of checking whether an arbitrary cell is part of a named range (that is, short of looping through an entire book's named ranges checking for intersections)? (It can be assumed that all the named ranges consist only of one cell).
View 6 Replies
View Related
Oct 3, 2013
I am trying to check the cells in column C for a defined set of rows and if it is blank change it to astericks.
Current is the row selected when the macro begins
LastRow is the last row with data (in column A)
For i = Current To LastRow
If Range("C" & i).Value = "" Then Range("C" & i).Value = "***"
Next i
View 6 Replies
View Related
Oct 16, 2013
I am using VBA to indent the values in a range of cells.
However, I would like my code to check if the label has already been indented, to prevent it from being indented further.
View 5 Replies
View Related
Aug 6, 2006
I want to run some macros based on the value of an input cell, but only if the input is a number (ie. it is not a letter or other character).
View 6 Replies
View Related
Apr 11, 2007
Is there any way through VBA that I can check whether the date in cell A2 is Monday or not.
Also the code should allow me to continue if its Monday else should promt a message saying that its not Monday and whether the user still wants to continue. If no it should terminate if yes the next part of the code should continue.
View 9 Replies
View Related
Mar 27, 2009
I have a cell in Excel having a boolean value and want to use a macro to check if it is true. Something like this:
View 3 Replies
View Related
Sep 29, 2006
I want to reformat some data, arranged as follows, and ignore blank cells
Date1 Time1 blank Time2
Date2 blank Time3
to
Date1 Time2
Date1 Time2
Date2 Time3
Code being used, currently writes out lines where a Date exists, but Time cell is blank, what do I need to modify in the code below, to ensure blank times are not written out
Sub test()
counter = 2
For i = 1 To Range("A65536").End(xlUp).Row
For j = 2 To Cells(i, 256).End(xlToLeft).Column
Sheets("Sheet2").Cells(counter, 1).Value = Cells(i, 1).Value
Sheets("Sheet2").Cells(counter, 2).Value = Cells(i, j).Value
counter = counter + 1
Next j
Next i
End Sub
View 3 Replies
View Related
Feb 28, 2007
Problem Designing "new customer" form, a textbox(forename) in the
form has the Control source of "B3" (an empty space in my Customer
database). When the form is run and the forname is typed in the box, it
fair enough appears in the Database sheet (in B3).
However, then when opening the form again and typing over the forename
just inserted it will ofcourse overwrite it (B3 is replaced). Any ideas how i can input
code into the sub linked to the textbox so that it checks the control
source and moves down if the source is empty(code checks B3 for text, moves to B4) then the next one (B4 is checked, moved to B5).
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, targetRange As Range
Set topCel = Range("B2")
Set bottomCel = Range("B65536").End(xlUp)
If topCel.Row > bottomCel.Row Then End
Set sourceRange = Range(topCel, bottomCel)
Set targetRange = Range("B3")
View 9 Replies
View Related
Jul 29, 2014
I am trying to build a user form to find out the customers who purchase more than $1,000 during a certain period. The userform has two inputs:
One is to select the data range of customer information. Assume all customer information are in the cell A2: H10, how shall I write the error checking code if the user selects the range which is out of (A2:H10)? message box would be " You selection include invalid data, please check"..
The other input is called " Get data past this data", and I can enter a date in the following cell ( txtDate). Regarding the error checking, I am thinking to use IsDate() function to make sure it is a valid date. Will be there be any other error checking you will recommend?
View 1 Replies
View Related
Nov 12, 2013
I'm trying to validate the data entered into a series of cells each cell can contain a different set of data but the value N/A is also permitted. For example:
Cell A1 could contain a date from 2013-01-01 thru 2013-12-01 but the value N/A is also valid
Cell A2 could contain a decimal from 0.01 thru 302502.23 but the value N/A is also valid
Cell A3 could contain an integer from 3 thru to 9000 but the value N/A is also valid
When the acceptable values are entered then I want to be able to carry on otherwise I want to pop up with an error.
View 10 Replies
View Related
Mar 23, 2009
I have a master sheet that has 3500ish names on it, and another sheet that I'll need to drop in a list of about 1000 names. What I need on the master is a way to check the dropped in data, find duplicated names, and flag them up.
Now, I believe what I did last time was have a true/false method of telling me if they're on both sheets, then use an IF formula to instead make Trues into "yes" and Falses into "no". However, I can't for the life of me figure out how I had it previously checking both sheets and confirming/denying if they're on both sheets or only appear once.
View 2 Replies
View Related
Mar 6, 2009
i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
View 10 Replies
View Related
Mar 6, 2009
i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
View 8 Replies
View Related
Mar 21, 2014
Is there a way of checking for duplicates in a range of cells using one cell only for the code? Return does not have to include what value is a duplicate - only true or false. This opposed to using multiple cell and COUNTIF or a pivot table.
View 5 Replies
View Related
Aug 2, 2008
I have a report that includes data broken up by several headings. Instead of showing the heading and the data below it, I would prefer that the heading was included in every subsequent row that it related to (and the heading be removed).
All the headings are in italic non bold format.
All the data is in standard format (no bold / italics / underline).
The only data in the sheet is as above, and there are no gaps in the data. A heading will relate to all the data directly below it until there is a new heading.
View 8 Replies
View Related
Aug 1, 2012
I am writing a macro that will allow me to copy all the data in a set range (A2 and below) after checking that B1 contains the text "Year_id".
Right now, I am able to copy all the information, and paste it onto "Sheet 4". I am unable to code for the part where the macro would check for the text. The code that I have (for copying-pasting the date) is below.
Any code that would check the information in B1 into this macro code below:
Code:
Sub Copy_Allinfo()
Dim Sht As Worksheet
Dim Rng As Range
For Each Sht In Sheets
If Sht.Name = "Sheet4" Then
[Code] ........
View 7 Replies
View Related
