Well I didn't even know Excel had a recorder (or a developer tab for that matter) until you prompted me.

Your modified code seems to work well. While maybe not obvious, but you may have guessed, I am trying to set up a client list with a unique ID. I will be using this list to populate a userform in a Word template that I use to create invoices.

I've got that part working. I can call a new invoice, call the list, make a selection and populate fields in the UserForm. From there I can populate fields in the invoice itself.

What I want to do now is add a process to manually enter a new client directly in the invoice userform and then have the ability to add this new client information programatically to my Excel data source.

I figured that I needed a unique ID and rather than look at every entry to see if it was previously used, I thought if the Client ID had to be unique in the data source then it would be a simple matter of trying to add and if the try failed then I've found a duplicate.

The method xld suggests and you provided works if I try to add and existing ID in the opened Excel file, but before I pull my hair out trying to add it programatically from a Word template can you tell me if this will even be possible? I mean wil the error that Excel creates when I have the sheet physically opened result in some run-time error that I can trap and advise the user that the client ID attempted is already in use?

Really my ultimate goal is that if the ID entered in the userform is not already used then a new entry is created in the Excel file. If it is already used then the user will be alerted that the entry already exists and if they continue the entry will be overwritten. I hope this makes sense and I hope you or someone can advise if what I am after is even possible.

Thanks.



Quote Originally Posted by fredlo2010
Hi gmaxey,

Like always xld with awesome ideas and suggestions. You can use the macro recorder to get a lot of information.

So here is the code implementing xld suggestion. There are other wasy to sort of get the same result. Like conditional formatting rules. I was actually trying that one out but I got an error in line ExecuteExel4Macro, so I gave up. I made some other changes to your code to shrink it a little ( and to apply what I am learning in the forum)

Some links here
http://www.mrexcel.com/forum/showthr...mula-Questions
http://www.ozgrid.com/Excel/highlight-duplicates.htm

Here is the modified code:

[vba]Function CreateDataSheet() As String
Dim strFname As String
Dim strPath As String
Const strCaption As String = "Save the Excel data file in a folder of your choice. " & _
"You may change the default name if you wish."
'ShowError 2, 8
'Unload frmNotification

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Set xlApp = CreateObject("Excel.Application")
End If

On Error GoTo 0
Set xlWB = xlApp.Workbooks.Add
Set xlSheet = xlWB.Sheets(1)

'rename the sheet to something meaningful
Application.Sheets(1).Name = "Data"

With xlSheet.Cells(1, 1).Resize(1, 6)
.Value = Array("Client ID", "Company Name", "Name", "Address", "e-mail", "Terms")
'I like my headers bold and centered but its up to you
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With

With xlSheet
.Cells(1, 1).ColumnWidth = 10
.Cells(1, 2).Resize(, 3).ColumnWidth = 30
.Cells(1, 4).ColumnWidth = 75
.Cells(1, 5).Resize(, 6).ColumnWidth = 20
End With


'use this to set the data validation up to row 100000000
With Range("A2:A1000000").Validation
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=COUNTIF($A$2:$A2,$A2)=1"
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "ID Check"
.ErrorMessage = "This ID is already in use please use a different one"
.ShowError = True
End With

strFname = xlApp.GetSaveAsFilename("CustomClientList.xlsx", _
"Excel files (*.xlsx),*.xlsx", 1, strCaption)
If strFname <> "False" Then
xlWB.SaveAs strFname
End If
strPath = xlWB.FullName
xlWB.Close SaveChanges:=False
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
CreateDataSheet = strPath

End Function
[/vba]

Thanks