vba

How to add data validation to a cell using VBA

by

Use this one: Dim ws As Worksheet Dim range1 As Range, rng As Range ‘change Sheet1 to suit Set ws = ThisWorkbook.Worksheets(“Sheet1”) Set range1 = ws.Range(“A1:A5”) Set rng = ws.Range(“B1″) With rng.Validation .Delete ‘delete previous validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:=”='” & ws.Name & “‘!” & range1.Address End With Note that when you’re using Dim … Read more

VBA, ADO.Connection and query parameters

by

You need to use an ADODB.Command object that you can add parameters to. Here’s basically what that looks like Sub adotest() Dim Cn As ADODB.Connection Dim Cm As ADODB.Command Dim Pm As ADODB.Parameter Dim Rs as ADODB.Recordset Set Cn = New ADODB.Connection Cn.Open “mystring” Set Cm = New ADODB.Command With Cm .ActiveConnection = Cn .CommandText … Read more

Imitating the “IN” Operator

by

I don’t think there is a very elegant solution. However, you could try: If Not IsError(Application.Match(x, Array(“Me”, “You”, “Dog”, “Boo”), False)) Then or you could write your own function: Function ISIN(x, StringSetElementsAsArray) ISIN = InStr(1, Join(StringSetElementsAsArray, Chr(0)), _ x, vbTextCompare) > 0 End Function Sub testIt() Dim x As String x = “Dog” MsgBox ISIN(x, … Read more

Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]

by

I think this is what you want… Sub SaveWorksheetsAsCsv() Dim WS As Excel.Worksheet Dim SaveToDirectory As String Dim CurrentWorkbook As String Dim CurrentFormat As Long CurrentWorkbook = ThisWorkbook.FullName CurrentFormat = ThisWorkbook.FileFormat ‘ Store current details for the workbook SaveToDirectory = “H:\test\” For Each WS In Application.ActiveWorkbook.Worksheets WS.SaveAs SaveToDirectory & WS.Name, xlCSV Next Application.DisplayAlerts = False … Read more

Unprotect VBProject from VB code

by

I have never been in favor of Sendkeys. They are reliable in some case but not always. I have a soft corner for API’s though. What you want can be achieved, however you have to ensure that workbook for which you want to un-protect the VBA has to be opened in a separate Excel Instance. … Read more

Excel Select vs Activate

by

Select allows selecting several objects at once. Objects that are selected are placed in the Selection object, which permits iteration. Selecting an object (say, a column) activates the object. Activating an object makes it the active object. Best way to think of it is “many cells can be selected, but only one may be the … Read more