General Excel question

The code you posted is from the "SameCell" sheet in Debra's sample workbook.

Are you sure you pasted it into the appropriate sheet module in your
workbook?

This code is event code and is "lunched"(sic) only when a selection is made
from a dropdown list in column C

If your dropdown is not in column C you must edit this line to your column
number

If Target.Column = 3 Then


Gord

On Thu, 18 Jun 2009 12:56:21 GMT, "dolpandotcom via OfficeKB.com"
u52610@uwe wrote:

Hi Gord,

Thanks for the link. I did download of the first link which enables multiple
data item selection into one cell. I copied the code into the VB editor in my
own worksheet. I lunched the MACRO in my vb editor layout, but i did not work
as it was with the downloaded sheet.

Pls see below the codes if I need to change any instruction :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub


Any other person can equally assist in this direction.
Thanks.
Dolpan
Gord Dibben wrote:
Question 1.

Download a sample workbook from Debra Dalgleish's site.

http://www.contextures.on.ca/excelfiles.html#DV0017

Question 2.

See Ron de Bruin's site

http://www.rondebruin.nl/calendar.htm

Question 3.

See Chip Pearson's site.

http://www.cpearson.com/excel/NoDupEntry.aspx

Gord Dibben MS Excel MVP

Hi Shane,

[quoted text clipped - 41 lines]
Dolpandotcom
06/16/09

Hi Gord,

Thanks a million. It is working fine. Since I needed it to work on 6 other
columns, I decided to make the If Target.Column = 3 Then to be IF Target.
Column=3 which makes it to include other columns that I want to use, but I
will like two or three items selected in a cell to be counted as one using
the COUNT(C8:j8) as an example.

Is there any other statement to add to the COUNTI(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

Thanks.
Dolpan

Gord Dibben wrote:
The code you posted is from the "SameCell" sheet in Debra's sample workbook.

Are you sure you pasted it into the appropriate sheet module in your
workbook?

This code is event code and is "lunched"(sic) only when a selection is made
from a dropdown list in column C

If your dropdown is not in column C you must edit this line to your column
number

If Target.Column = 3 Then

Gord

Hi Gord,

[quoted text clipped - 73 lines]
Dolpandotcom
06/16/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200906/1

=COUNTA(C8:J8) counts multiple entries in a cell as one.

Also......I would change If Target.Column = 3 Then to

If Not Application.Intersect(Target, Columns("C:H")) Is Nothing Then

Nothing wrong with your revision except it potentially includes columns past
H


Gord


On Thu, 18 Jun 2009 16:05:50 GMT, "dolpandotcom via OfficeKB.com"
u52610@uwe wrote:

Hi Gord,

Thanks a million. It is working fine. Since I needed it to work on 6 other
columns, I decided to make the If Target.Column = 3 Then to be IF Target.
Column=3 which makes it to include other columns that I want to use, but I
will like two or three items selected in a cell to be counted as one using
the COUNT(C8:j8) as an example.

Is there any other statement to add to the COUNTI(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

Thanks.
Dolpan

Gord Dibben wrote:
The code you posted is from the "SameCell" sheet in Debra's sample workbook.

Are you sure you pasted it into the appropriate sheet module in your
workbook?

This code is event code and is "lunched"(sic) only when a selection is made
from a dropdown list in column C

If your dropdown is not in column C you must edit this line to your column
number

If Target.Column = 3 Then

Gord

Hi Gord,

[quoted text clipped - 73 lines]
Dolpandotcom
06/16/09

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one
row of data as shown below::

A B
C D
2 2,1
1

If I want to count the numbers that exist on that row as COUNT(Ax:Cx) whrer x
is the row number. The result which will be placed in cell Dx will not be 3
simply because the cell Bx is taken as character probably. My humble question
is that "How do I use the COUNT function to accomplish the task or is ther
any other function that I can use?

Thanks.
Dolpan

Gord Dibben wrote:
=COUNTA(C8:J8) counts multiple entries in a cell as one.

Also......I would change If Target.Column = 3 Then to

If Not Application.Intersect(Target, Columns("C:H")) Is Nothing Then

Nothing wrong with your revision except it potentially includes columns past
H

Gord

Hi Gord,

[quoted text clipped - 30 lines]
Dolpandotcom
06/16/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200906/1

I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?


Gord

On Thu, 18 Jun 2009 19:17:08 GMT, "dolpandotcom via OfficeKB.com"
u52610@uwe wrote:

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one
row of data as shown below::

A B
C D
2 2,1
1

If I want to count the numbers that exist on that row as COUNT(Ax:Cx) whrer x
is the row number. The result which will be placed in cell Dx will not be 3
simply because the cell Bx is taken as character probably. My humble question
is that "How do I use the COUNT function to accomplish the task or is ther
any other function that I can use?

Thanks.
Dolpan

Gord Dibben wrote:
=COUNTA(C8:J8) counts multiple entries in a cell as one.

Also......I would change If Target.Column = 3 Then to

If Not Application.Intersect(Target, Columns("C:H")) Is Nothing Then

Nothing wrong with your revision except it potentially includes columns past
H

Gord

Hi Gord,

[quoted text clipped - 30 lines]
Dolpandotcom
06/16/09

Your COUNTA function is absolutely correct. It has done what I want exactly.

Thanks.
Dolpan

Gord Dibben wrote:
I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?

Gord

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one
[quoted text clipped - 30 lines]
Dolpandotcom
06/16/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200906/1

Hi Gord,

Happy Sunday. I am here again. I tried to use the calendar program in a
worksheet. I did change the cell where I will like to automate the calendar
as so that when one clicks on that cell, the calendar pop up for appropriate
date selection. It gave me an error "Run-time error, Object required. Is it
that it cannot work in excel2003 or I did not change a statement somewhere in
the program?

This is the program which I pasted but only changed the cell A1:A20 to A1
since that is the position where I want the calendar to pop up.

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub


Thanks and enjoy your Sunday.
Dolpan

Gord Dibben wrote:
I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?

Gord

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one
[quoted text clipped - 30 lines]
Dolpandotcom
06/16/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200906/1

I am sending same message again. It was like I did not have a good internet
connection. Find posting below:

Hi Gord,

Happy Sunday. I am here again. I tried to use the calendar program in a
worksheet. I did change the cell where I will like to automate the calendar
as so that when one clicks on that cell, the calendar pop up for appropriate
date selection. It gave me an error "Run-time error, Object required. Is it
that it cannot work in excel2003 or I did not change a statement somewhere in
the program?

This is the program which I pasted but only changed the cell A1:A20 to A1
since that is the position where I want the calendar to pop up.

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub

Thanks and enjoy your Sunday.
Dolpan



Gord Dibben wrote:
I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?

Gord

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one
[quoted text clipped - 30 lines]
Dolpandotcom
06/16/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200906/1

Did you first insert the calendar activex form from the control toolboxmore
controls on your sheet?

Do you have MS Access installed..................you won't have the needed
mscal.ocx file if you haven't.

See Ron de Bruin's site for details and instructions.

http://www.rondebruin.nl/calendar.htm


Gord

On Sun, 21 Jun 2009 14:37:16 GMT, "dolpandotcom via OfficeKB.com"
u52610@uwe wrote:

Hi Gord,

Happy Sunday. I am here again. I tried to use the calendar program in a
worksheet. I did change the cell where I will like to automate the calendar
as so that when one clicks on that cell, the calendar pop up for appropriate
date selection. It gave me an error "Run-time error, Object required. Is it
that it cannot work in excel2003 or I did not change a statement somewhere in
the program?

This is the program which I pasted but only changed the cell A1:A20 to A1
since that is the position where I want the calendar to pop up.

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub


Thanks and enjoy your Sunday.
Dolpan

Gord Dibben wrote:
I guess I misunderstood this statement................

Is there any other statement to add to the COUNT(C8:j8) to enable count two
or more items selected in a cell as one (1) please?

You have A1=2, B1=2,1 and C1=1

What do you want as a result in D1?

COUNT will give you 2 because it counts numbers only

COUNTA will give you 3 which seems to be what your starement above asks for.

Do you want to count B1 as two numbers giving you a return of 4 in D1?

Gord

I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one
[quoted text clipped - 30 lines]
Dolpandotcom
06/16/09

I am using excel2003 and the instuction says that by simply selecting from
the active worksheet excel menu "Object" and further select "Calendar" it
will place the calendar on the sheet. It is like the VB code given only works
for excel 2007. Am I right pls?

Dolpan






Gord Dibben wrote:
Did you first insert the calendar activex form from the control toolboxmore
controls on your sheet?

Do you have MS Access installed..................you won't have the needed
mscal.ocx file if you haven't.

See Ron de Bruin's site for details and instructions.

http://www.rondebruin.nl/calendar.htm

Gord

Hi Gord,

[quoted text clipped - 51 lines]
Dolpandotcom
06/16/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200906/1