Hi Team, I am trying to trigger a macro whenever a new mail is recieved in the outlook. Here is my code :

Private Sub Application_NewMail()

'To set the email folder to INBOX, set to MAPI
Dim onamespace As Outlook.NameSpace
Set onamespace = Outlook.GetNamespace("MAPI")

Dim myfolder As Outlook.Folder
Set myfolder = onamespace.GetDefaultFolder(olFolderInbox)

Dim email As Outlook.MailItem
Set email = Outlook.CreateItem(olMailItem)
Dim attachment As Outlook.attachment

For Each email In myfolder.Items
MsgBox email.SenderName
Next email
End Sub

But i am not getting the new mail which i sent at present. myFolder.Items is pointing to inbox but fetching older mails I am getting the "older" mails. Can anyone help me on where i am going wrong here Thanks Manju

Hi all, I have one macro which concatenates cells if repeated found in other cell but it is not working if cell is empty and we have some value in other cell. For example input is A B No Colour 1 Red 1 White 2 Black 2 Orange Blue Purple My output is showing as D E No Combined Colour 1 Red,White 2 Black,Orange In this case Blue and Purple and getting lost. I am looking for something like : My output should be D E No Combined Colour 1 Red,White 2 Black,Orange Blue Purple Below is my macro:

Sub ConcatenateCellsIfSameValues()
Dim xCol As New Collection
Dim xSrc As Variant
Dim xRes() As Variant
Dim I As Long
Dim J As Long
Dim xRg As Range
xSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(, 2)
Set xRg = Range("D1")
On Error Resume Next
For I = 2 To UBound(xSrc)
xCol.Add xSrc(I, 1), TypeName(xSrc(I, 1)) & CStr(xSrc(I, 1))
Next I
On Error GoTo 0
ReDim xRes(1 To xCol.Count + 1, 1 To 2)
xRes(1, 1) = "No"
xRes(1, 2) = "Combined Color"
For I = 1 To xCol.Count
xRes(I + 1, 1) = xCol(I)
For J = 2 To UBound(xSrc)
If xSrc(J, 1) = xRes(I + 1, 1) Then
xRes(I + 1, 2) = xRes(I + 1, 2) & ", " & xSrc(J, 2)
End If
Next J
xRes(I + 1, 2) = Mid(xRes(I + 1, 2), 2)
Next I
Set xRg = xRg.Resize(UBound(xRes, 1), UBound(xRes, 2))
xRg.NumberFormat = "@"
xRg = xRes
xRg.EntireColumn.AutoFit
End Sub

Appreciated for any help and corrections in my code. Thanks Manju

Hi Dave, Thanks for your reply. Sorry did not noticed your reply so deleted the question posted. Sorry for deleting the post added. Thanks Manju

Hi all, I have created one Add-in(.xlam) and added that into my excel->Option->enabled on my desktop and used it in one of my application and its working fine. But when i try to run that application in different user machine i am unable to see the output as expected as the .xlam is not available in that machine. I dont want to copy the .xlam to the user machine. Can someone help me how can i use the .xlam used application in other user machine without copying the .xlam on their machine. Any suggestions will be appreciated! Thanks Manju

Hi Team, I have 3 columns A,C and F. Based on A and C, i am writing the formula for adding F cells together in the Output cell. I have the formula written for output column as

=IF($A1=0,F1,
IF($A1=1,IF(C1=4,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4,
IF(C1=3,F1 &CHAR(10)& F2 &CHAR(10)& F3,
IF(C1=2,F1 &CHAR(10)& F2,""))),""))

Its working fine as below:

A C F output
0 1 a a
1 2 b bc
2 2 c
1 3 d def
2 3 e
3 3 f

Can someone help me how can i add the formula in loop to check for C1>4 like and go on for if more values :

IF($A1=1,IF(C1=5,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4 &CHAR(10)& F5,
IF($A1=1,IF(C1=6,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4 &CHAR(10)& F5 &CHAR(10)& F6,
IF($A1=1,IF(C1=7,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4 &CHAR(10)& F5 &CHAR(10)& F6 &CHAR(10)& F7,

The VB Code shows below:

Range("J5").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1=0,RC[-5],IF(RC1=1,IF(RC[-7]=4,RC[-5]&CHAR(10)&R[1]C[-5]&CHAR(10)&R[2]C[-5]&CHAR(10)&R[3]C[-5],IF(RC[-7]=3,RC[-5]&CHAR(10)&R[1]C[-5]&CHAR(10)&R[2]C[-5],IF(RC[-7]=2,RC[-5]&CHAR(10)&R[1]C[-5],""""))),""""))"
Range("J5").Select
Selection.AutoFill Destination:=Range("J5:J262"), Type:=xlFillDefault
Range("J5:J262").Select

. I have written my own formula(function) but confused how can i assign that function to this cell as this the output excel and cannot add/insert as module and call from cell: Its working fine when tested in excel individually but when i tried to change the VB code and replace the function its not working: Here is the below code:

Range("J5").Select
ActiveCell.FormulaR1C1 = "Lookup_concat(J5,C2:C6,D2:D6)"
Range("J5").Select
Selection.AutoFill Destination:=Range("J5:J262"), Type:=xlFillDefault
Range("J5:J262").Select

Can anyone suggest any solution for me how can i assign this function to the cell : The formula written is :

Option Explicit

Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)

Dim i As Long
Dim temp() As Variant
Dim result As String
ReDim temp(0)

For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
temp(UBound(temp)) = Return_val_col.Cells(i, 1).Value
ReDim Preserve temp(UBound(temp) + 1)
End If
Next

If temp(0) <> "" Then
ReDim Preserve temp(UBound(temp) - 1)
Unique temp
For i = LBound(temp) To UBound(temp)

Hi Team, I have 3 columns A,C and F. Based on A and C, i am writing the formula for adding F cells together in the Output cell. I have the formula written for output column as

=IF($A1=0,F1,
IF($A1=1,IF(C1=4,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4,
IF(C1=3,F1 &CHAR(10)& F2 &CHAR(10)& F3,
IF(C1=2,F1 &CHAR(10)& F2,""))),""))

Its working fine as below:

A C F output
0 1 a a
1 2 b bc
2 2 c
1 3 d def
2 3 e
3 3 f

Can someone help me how can i add the formula in loop to check for C1>4 like and go on for if more values :

IF($A1=1,IF(C1=5,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4 &CHAR(10)& F5,
IF($A1=1,IF(C1=6,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4 &CHAR(10)& F5 &CHAR(10)& F6,
IF($A1=1,IF(C1=7,F1 &CHAR(10)& F2 &CHAR(10)& F3 &CHAR(10)& F4 &CHAR(10)& F5 &CHAR(10)& F6 &CHAR(10)& F7,

Great thanks Manju

Thanks richard. I will try it out. Thanks Manju

Dim splitvals As Variant
Dim totalVals As Long
Dim myData As Workbook
Set myData = Workbooks.Open("D:\Test.xlsx")

Set sh1 = myData.Sheets(1)
Set sh2 = myData.Sheets(2)
sh2.Cells.Clear
lrow1 = sh1.Range("A65356").End(xlUp).Row

For j = 2 To lrow1
splitvals = Split(sh1.Cells(j, 2), ",")

For i = LBound(splitvals) To UBound(splitvals)
    lrow2 = sh2.Range("B65356").End(xlUp).Row
    lrow3 = sh2.Range("A65356").End(xlUp).Row
    sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
    sh2.Cells(lrow3 + 1, 2) = splitvals(i)
Next i   

Next j

Thanks Sharan

Hi, I have updated the code where it loads the file from some path. And setting it to myData.

Set myData = Workbooks.Open("D:\B_Test.xlsx")
Set sh1 = myData.Sheets(1)
Set sh2 = myData.Sheets(2)

Now how can i change the code so that it writes to a new workbook and and set that to myData.

Set myData = "New WorkBook"

Thanks Sharan

Hi Team, I am trying to split the cells if comma and space . I tried below code.Its spliting for space now. How can i change it so that it will check for multiple(both space and comma)

Dim splitvals As Variant
Dim totalVals As Long

Set sh1 = ThisWorkbook.Sheets(1)
Set sh2 = ThisWorkbook.Sheets(2)

sh2.Cells.Clear
lrow1 = sh1.Range("A65356").End(xlUp).Row

For j = 2 To lrow1
splitvals = Split(sh1.Cells(j, 2), " ")
For i = LBound(splitvals) To UBound(splitvals)
lrow2 = sh2.Range("B65356").End(xlUp).Row
lrow3 = sh2.Range("A65356").End(xlUp).Row
sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
sh2.Cells(lrow3 + 1, 2) = splitvals(i)
Next i

Next j

Even i tried:

splitvals = Split(sh1.Cells(j, 2), ", " " ")

Please suggest me . Thanks Manju

Hi all, I need to load an Test.xlxs file form "D" drive and write the same content to new workbook in sheet1. Add sheet2 in the workbook and filter the workbook and write the filtered into sheet2 to on workbook. I have tried the below code but its writing to the same input file with sheet2 but not in the new workbook. Input file will contain

DrinkID Reciepe_Dat
2 Absolute,Enter,Test
3 Decupper,Enter,Test
4 "Absolute
"

Output is having

DrinkID Reciepe_Dat
2 Absolute
2 Enter
2 Test
3 Decupper
3 Enter
3 Test
4 "Absolute
"

but writing to the same input file. here is my code:

Sub splitbycells()
Dim splitvals As Variant
Dim totalVals As Long

Set sh1 = ThisWorkbook.Sheets(1)
Set sh2 = ThisWorkbook.Sheets(2)

sh2.Cells.Clear
lrow1 = sh1.Range("A65356").End(xlUp).Row

For j = 2 To lrow1
'splitvals = Split(sh1.Cells(j, 2), Chr(10))
splitvals = Split(sh1.Cells(j, 2), ",")

For i = LBound(splitvals) To UBound(splitvals)
    lrow2 = sh2.Range("B65356").End(xlUp).Row
    lrow3 = sh2.Range("A65356").End(xlUp).Row
    sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
    sh2.Cells(lrow3 + 1, 2) = splitvals(i)
Next i

Next j
sh2.Range("A1") = "DrinkID"
sh2.Range("B1") = "Reciepe_Dat"

End Sub

Thanks Sharan

Hi Team, I have a existing .xlsm file which I was able to open in older excel versions(like 2010) successfully. But i am unable to open the same .xlsm file in newer office versions 2016.OPening as blank. Tried changing the settings under Excel->File->Options->Advanced Under "General" ->Unchecking the "DDE" Under "Display :" ->Unchecked the "Disable hardware graphics acceleration" ->Turn off add-ins. Anyone faced this issue ? Any help will be really appreciated. Thanks in advance. Thanks Manju

Hi Team, DateTimePicker control has the below menu options on Mouse Right Click : Cut Copy Paste No Date/Time We can select any of this from the menu Can anyone let me know how can i trigger this clicked events ? Tried below code:

private void dateTimePicker_MouseDown(object sender, MouseEventArgs e)
{
if (e.Button == MouseButtons.Right)
{
//How can i know whether user selects Cut/Copy/Paste/No DateTime
}
}

Thanks Sharan

Thank you for your suggestions. I have achieved it using ParseExact function with some formatting the DateTime value. Thanks Sharan

Do we have any event for Paste where I can trigger and reformat the Date and paste it in the control ?

Thanks for all your responses Is there a way where I can change the .MaxValue Property? Presently it displays in MM/DD/YYYY(12/31/9999 11:59 PM). Can I set it to DD/MM/YYYY(31/12/1999).

Yes. Copy in DD/MM/YY format and paste the same in spreadsheet. Edit the DateTime in spreadsheet. Copy from spreadsheet and paste back to the DateTime control. But DateTime control allows MM/DD/YY format. That's the issue :(

I have not added any code specific for copy paste. I have just changed the property of DateTimePicker is.dateTimePicker.CustomFormat = "dd/MM/yy HH:mm:ss"; is.dateTimePicker.Format = System.Windows.Forms.DateTimePickerFormat.Custom; is.dateTimePickerExecTime.ShowUpDown = true; Once right click on Control I select on Copy it copies and I copy it in excel . From excel I again copy and trying to paste it on control.

Hi Team, Need your help on Copy/Paste on DateTimePicker. I am displaying the UI in DD/MM/YY format and when I copy it copies in the same format(DD/MM/YY). But Paste uses MM/DD/YY format. How can i change the Paste format to DD/MM/YY? Example UI is displaying 16/02/18(which is todays date in DD/MM/YY) and when I copy it copies as it is 16/12/18. Now I modify it as tomorrows date 17/12/18 I does not allow as it validates for MM/DD/YY and 17 is greater then 12 so it does not copy. How can i change the format to DD/MM/YY before pasting? Thanks Sharan

Hi Team, How can i call function from Forms through any of the controls? Suppose i have function Validate() in FrmTest.cs under Global\Forms\ FrmTest.cs private void Validate() { } And control TestControl.cs under Global\Control\ TestControl.cs For any drop down event changed,i need to invoke the function Validate() from FrmTest.cs. Any suggestions will be great helpful Thanks in advance Manju