Free Microsoft Excel 2013 Quick Reference

Using worksheet_selectionchange to prevent null cells ???

Does anyone have an example of code I might use with
"worksheet_selectionchange" to prevent a user of my workbook from leaving
cells null withing a specific range? Note that users may add rows to the
middle of the range, but none of the cells in the added rows withing the
range should be null. Ideal case would result in a pop-up when the user
tried to leave the worksheet.

THANKS FOR ANY DIRECTION!!


Post your answer or comment

comments powered by Disqus
something happened to my worksheet that I can't use arrow keys to move from cell to cell but have to hit enter. How do I fix it back?

Hi Everyone,

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads
Mortgage
Rent

I would like to have a custom format in a different cell that conditionally will read:

"Rent:" #.00 "Per Sq. Ft."

or

"Mortgage:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated

I know that you can use CTRL+click to select multiple cells/ranges, but is there any way to DESELECT cells?

I cannot count how many times I have gone through my workbooks to apply formatting to certain ranges of different size, only to accidentally select one range a bit to big and have to reselect everything.

I dont know how to program VB so I just use the macro recorder. I have 15 macros recorded and I need a way to use macro recorder to query a cell and run the corresponding macro assigned to that value (1-15). So if the cell it querys has a 3 in it, it would run macro "three" and then automatically query the next highlighted cell (the cell the previous macro ended on) and so on. Not sure if it can be done, but if it can, itll save me hours.

Thanx guys.

This line doesn't work:
ActiveCell.Value = InputBox("What is the New Entry?")

This line does work but I can't use it:
Range("A30").Value = InputBox("What is the New Entry?")
Problem is, I don't actually know the cell address, I'm using preceding code to find the cell I want.

Sub
NewEntry()

'goes to the first empty cell at the end of a validation list
    Sheets("Validation Lists").Select
    Range("First_List").End(xlDown).Select
    ActiveCell.Offset(1, 0).Activate
    
'enters InputBox data
    ActiveCell.Value = InputBox("What is the New Entry?")
'THE LINE ABOVE DOESN'T WORK

End Sub
What is my problem?
TIA

Hello

I am trying to use conditional formatting to shade specific cells automatically.
I have attached my spreadsheet for reference.

Basically I am trying to produce a gantt chart, which colours in the relevant cells when I change the dates for the listed task.

I have two problems:

1. I can get the conditional format to work (to a point) using dates. At the moment I have columns H & I with the dates in. Then I have applied the conditional format of
=AND(N$6>=$H7,N$6<$I7)=TRUE

Unfortunately this does not fill in the last cell that I want. For exmaple in row 7 this is Q7.
How do I amend the formula to include the last cell I need? The formula seems to do up this date but not including.

2. My next problem is that ideally I don't want to use dates to achieve this. My business works in set periods. Basically there are 13 periods in a year, each containing 4 weeks. You will see that columns N to BM have been set out to this timescale.
I would like to be able to enter the start date in columns F7 & G7 and the finish date in columns J7 & K7 and then the relevant cells from column N onwards are highlighted (like the way I have done with dates but instead using P1, 1 to mean start Period 1, week 1 etc. I would delete the date columns (H & I) completely.

Does anyone know if this is possible and how I reorganise my spreadsheet and conditional formatting to achieve this?

Any help is very much appreciated.
Thanks

Debbie

I'm trying to write a macro that will be triggered when the user clicks an in-sheet command button. The button prints the worksheet, and I want the macro to check multiple cells and display an error message and prevent printing if any are blank. Ideally I want it to stop the print when it finds the first blank cell.

This is the macro that prints the worksheet:

Code:
 Private Sub PrintWorksheet_Click()

'Quick print of active worksheet to default printer upon PrintWorksheet 
'cmd button click.  No printer dialog box.
    
Call BlankCheck

ActiveSheet.PrintOut

End Sub
And this is the macro that checks for blank cells:

Code:
Sub BlankCheck()

Dim blnEmptyCell As Boolean
blnEmptyCell = False

If Sheets("Sheet1").Range("B13") = "" Then blnEmptyCell = True
If Sheets("Sheet1").Range("B14") = "" Then blnEmptyCell = True

If blnEmptyCell = True Then MsgBox "One or more of the required fields is 
blank.  Please complete all required fields."


Exit Sub

Cancel = True

End Sub
The problem I have is that the second macro keeps checking cells, and displaying error messages each time it find a blank. I have a lot more cells to check, 36 total. Also, is there a cleaner way to structure the macro so I'm not checking one cell per command line?

I'm very new to VBA. Most of my code has been liberally borrowed from this site, and cobbled together into a functioning spreadsheet. Any assistance, or other tidbits of wisdom you can toss my way are appreciated.

I almost forgot, I'm using Excel 2000 on Win XP Pro, SP2.

Good evening everyone! (:

I am new in VBA and since a few days I am stuck while trying to create an hyperlink from the cell of a worksheet to another cell in a different worksheet but within the same workbook.

I am working on two worksheet called COUNTRIES and ERROR CHECK.
"i" and "dc" are two integer variables which scroll all cells in the worksheet COUNTRIES (i all rows and DC all columns) with two DO-LOOP functions, until the first empty cell: which represent the end of my database.
The 2 variables i and DC simply check cell by cell, if there were errors while inputing the data.

So: if the program finds a mistake in the cell H14: the variables will be equal to: i=8 and dc = 14.
If the mistake was in A1, the variables will be equal to: i=1 and dc=1.

Everytime the program finds mistakes in the database (for example a NON Valid Date), then: a WARNING for each specific error is written in the worksheet ERROR CHECK.
The users of the database will read all the warning found: they can click in each WARNINGl, so they will be automatically redirected to the Database (worksheet COUNTRIES), in the exact cell where the error was found... so they can correct the error straight away.

Here my wrong code:

With wbBook 
    Set wsCountries = .Worksheets("Countries") 
    Set wsErrors = .Worksheets("ERRORS CHECK") 
End With 

'.......'

With wbBook 
    Set Selez = wsErrors.Cells(k, j) 
    wsErrors.Hyperlinks.Add Selez, " #' " & wsCountries.Name & " ' ", subaddress= "cell(i,
dc)" 
End With
So:
1)
is the cell with the WARNING in which I want to add the Hyperlink....

2) When people click on the hyperlink: the destination will be the worksheet wsCountries and the cell will be the one in which the input-error was found:
 where i and dc will have the value of the row and the column where the error was found.

The number of hyperlinks created, is proportional to the number of errors found by the program while "checking" the database COUNTRIES.

Concluding: instead of using an explicit address, like...


...I need to replace A2 with the dynamic Variables i and dc.

Does anyone knows how to write the syntax for this?

Thank you very much in advance, I hope it was clear!

Greetings

Emanuele

using Windows XP excel 2002

I need help in trying to figure out how to take the entered value in a row that has been selected from a list through data validation and have that value for that item go to the top of my worksheet for a total by code(list).
ie:

TOTAL Hrs
M T W
201 7 7
205 10 1
206 7 16

Joe B
(list of codes in cell has now been selected to show data validate from maste r list)
code M T W
201 7 7
205 1 1
Kevin H
205 9
206 9

Mark L
206 7 7

so as each employee's time is selected from a list of cost codes the total hours column at the top of the page can "take" each codes' time to the correlating total code.
We have about 50 data validations on the "list" for each employee's cell. I am trying to find a way that it will recognize the "cost code selected" then take each hour up to the top for each day.

I could send a copy of the worksheet page to show the cells validation's list and what we hope to accomplish if you need it.
Thank you

Allison

I am new to VBA but have found and used the code below to rename a worksheet tab. I would like to automatically rename the worksheet based on information in cell $A1$1. However, the code below works only when I double-click cell $A$1 and click on the checkmark tab in the formula bar.

I would like to augment the code (or have new code) that automatically changes the worksheet tab to match the cell contents without requiring use of the enter button, running a macro, etc.

Any help would be appreciated.

Thanks.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then Sh.Name = Left(Target, 30)
End Sub

I've tried to combine data from few cells using "CONCATENATE" function. But,
when a cell is blank, a blank appears in the result. How to prevent this
blank?

Hi,

I have a workbook with about 40 sheets. The first sheet (a
summary/contents page) has a list with hyperlinks to individual sheets
(named 1 through 38). The list can be sorted a number of ways so that
a return hyperlink from the individual sheet does not always go to the
relevant cell on the first sheet (because of any sorting done after
the link was created). Now my sheets are named 1 to 38 and these
correspond to the list in the first sheet(ie: 38 lines of data). What
I was hoping for was a macro that I could use from the individual
sheets that would recognise the sheet name, say, 25 and then go to the
cell containing 25 in the list on the first sheet, which is by the way
called "list"?

Thanks for any help you can provide.

Hi all - I'm new to VBA but recently found a very useful code on the
web. I am using it to display values from specific cells in a data
validation box...neat little feature. If you click on a cell in column
B, a data validation box displays with info from other cells (that I
specified in the code), so you can see info from those cells upfront
without scrolling to the left. The only thing is that when a cell is
null (for instance cell 12 and 13), an empty line will display in it's
place in the data validation box (see code below). What code I can add
so that it ignores the null cells and doesn't display a blank lines?
I'm hoping there's something I can use. If anyone can help me, I'd
greatly appreciate it!! Thanks a bunch!

Here's the code:
____________________________________
Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Cells(2, 18) = False Then Exit Sub
With Target
If .Column = 2 And _
.Row > 2 And _
.Row < ActiveSheet.UsedRange.Row + _
ActiveSheet.UsedRange.Rows.Count Then
.EntireColumn.Validation.Delete
With .Validation
.Add Type:=xlValidateInputOnly
.InputTitle = "" ' Optional
.InputMessage = Cells(Target.Row, 10) & Chr(10) & _
Cells(Target.Row, 11) & Chr(10) & _
Cells(Target.Row, 12) & Chr(10) & _
Cells(Target.Row, 13) & Chr(10) & _
Cells(Target.Row, 14) & Chr(10) & _
Cells(Target.Row, 15) & Chr(10) & _
Cells(Target.Row, 16)

End With
End If
End With
End Sub
________________________________________

hi frns

I have a simple question, yet hard to answer for me ...

assume that we have a code sth like

sub countcells()

dim n as variant
dim counter as integer

Worksheets("data").Select

For Each n In ActiveSheet.Range("e:e")
If n.Value <> 0 Then
counter = counter + 1
End If
Next n

End sub

as u can see we are counting the cells that are not equal to zero on
the column "e" here.

now i gotta use that counter to refer to a cell. it sud be sth like ex:
[a1:a+]

here plus sud be the counter that i define ....

what i wanna do is to copy the cells which have a value. But range may
differ everyday. So i sud count them.

i have searched for all kinds of references but cudn't find a
solution...

any help appreciated frnds ...

thx u all....

Using ADDRESS function I am able to search a table for a specific value and place the address in a cell.
I want to use that address to update the cell's value.

For example, a table of dates named dateBox is searched for a specific date using the ADDRESS function.

When found, the address of the cell containing the date is placed in A15. Lets say the address found is DM12

I need to take the current value residing in A15, add 14 to it and replace the old value in DM12 with the new value.

Is there a worksheet function to handle this?

Thanks in advance.

Hi,

I originally had a post on here where i wanted an change-event to clear two cells.

HTML Code: 

Now instead of clearing the two cells i want them to be populated with data in a different column along the same row.

For example:
If the Status cell is changed then i want the "Date" column to be todays date. Also, if the status cell value changes i want the "Reviewer's Intials" to be set equal to the value in the "Owners" column.

Any idea on how to do this? I tried a few things and you can see what i tried in teh vba code on the "Sheet4" section.

I am enclosing a file as an example.

I've tried to combine data from few cells using "CONCATENATE" function. But,
when a cell is blank, a blank appears in the result. How to prevent this
blank?

In Excel 2000 VBA, Is it possible to use "For... Each" to iterate through a Cells collection?

Something along the lines of:

	VB:
	
 
Dim celX As Cell ' I realze that Excel's VBA doesn't actually have a Cell object
 
 
 ' Iterate Through rngMyRange's Cells Collection
For Each celX In rngMyRange.Cells 
     
    MsgBox(celX.Value) 
     
Next celX 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
While I realize the above code won't actually work, IS there a way to use For Each with the Cells collection?

Presently, I'm using the Count property to get the number of cells and using that value in a For Loop iteration test.

Here's an example:

	VB:
	
Sub ForLoopCell() 
     
    Dim iCntr As Integer, rngTest As Range 
     
    Set rngTest = ActiveSheet.Range("B1:B10") 
     
     
     ' Iterate Through Cells Collection By Using A Counter
    For iCntr = 1 To rngTest.Cells.Count 
         
        MsgBox (rngTest.Cells(iCntr).Value) 
         
    Next iCntr 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Using a For... Each would be easier, but is there a way to do it?

THANKS VERY MUCH!!

I have 2 Columns A and B with following values:

Values in Cells A2 to A11 are:
1.30
1.35
1.31
1.41
1.40
1.31
1.41
1.30
1.40
1.35

Values in Cells B2 to B11 are:
4
4
3
4
4
4
3
2
2
1

The result I need is :

If range in Column A (A2 to A11) = 1.31 to 1.40,
count the NO OF CELLS in Column B (B2 to B11) where the value = 4.

QUESTION: How to use worksheet function to do it?

(The correct answer should be 3)

Hi guys...
I've got a macro where I want to use a counter variable, then use that variable to reference a cell.
eg... if I've got a variable called intCount, I want to reference Column Q, row intCount... I've tried Range(Q(intCount)) but it doesn't work... any thoughts???

For example, if i have the value 5 in B1 and i want to use that value to reference a cell in column C, ie the formula for A1 is something like:

=C(B1)

obviously that doesn't work, but i hope you get the just of it.
Thanks!

I am using a spreadsheet to track print jobs. One of the things I track is
the type of paper used and the cost of each piece for each job. Each paper
stock is assigned a stock code and has a unique cost. I want to be able to
enter the code and have the cost of the paper automatically come up in the
next cell. I currently have 16 different codes and each one has a different
cost. For instance, I want to enter the code PS1 in cell K5 and want it to
put the cost associated with PS1 (.03) in cell L5. I have tried to use the IF
function, but you can only nest 7 in a cell (I think). Any ideas? Any help
would be appreciated. Thanks!

Hi all.

I have an excel sheet with Col A and Col B. What I want to do is that I want to prevent the users from typing anything in Col B until they have typed anything in Col A. Basically the row in col A should be filled before filling the same row in col B.

eg: If col A is NUMBER and col B is USER,
then USER value should come only if there is a corresponding value in col A in the same row.

Any help is appreciated. Thanks

Hi,

I am looking for a way to select a cell by using a number,

for example i want to use the number 5 (A5=5) to select cell D5 end to put it at location Z1 so i something like: Z1= D & A5

I don't want to use Vlookup. Is this possible or do i have to use Vlookup?
Or should i use Visual Basics editor? if yes can somebody help me with that?

Thanks Michiel


No luck finding an answer? You could always try Google.