Tag Info

Hot answers tagged

3

You can use the environ("username") to get the user's Window's Logon ID. You can use that to determine if the macro should run or not.


2

You were using range.value("B2") rather than range("B2").value - Sub ifcitythencity() If InStr(1, (range("A2").Value), "San Francisco") > 0 Then range("B2").Value = "San Francisco" End Sub The error argument not optional gives you the clue as to the problem - the range(arg) object has to have an argument within the ().


2

Think I've managed to find my answer - sadly this is not possible. This MSDN blog post by Excel MVP Zack Barresse says: Using timelines is as easy as point and click. When looking at the control you will see the filtered range colored, with each end containing a vertical ellipses, which you can click and drag to where you want (as seen below). In ...


2

If we have this in Sheet1: and run this short macro: Sub ReOrganize() Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Sheets("Sheet1") Set sh2 = Sheets("Sheet2") Dim N As Long, M As Long, i As Long, j As Long, K As Long Dim t1 As String, t2 As String N = sh1.Cells(Rows.Count, "A").End(xlUp).Row K = 1 For i = 1 To N ...


2

To use RGB you simply use Sheet1.Cells(i, 1).Interior.Color = RGB(0,255,0) You can then review this site, which shows you the color index, the HEX and the RGB equivalent of each http://dmcritchie.mvps.org/excel/colors.htm So, in the example above this is the same as Color.Index 4 (green)


1

There's an option to prompt for filename. Go to Data / Connections, choose your connection, click properties. Under the refresh control section, on the Usage tab, take the tick out of the box "prompt for file name on refresh"


1

You code works with Outlook Inspector (a separate message window). In order to be able to manipulate the right-side preview pane text, you need to use the ActiveExplorer's ActiveInlineResponseWordEditor property like this: Set Editor = ActiveExplorer.ActiveInlineResponseWordEditor If Editor Is Nothing And Not ActiveInspector Is Nothing Then Set Editor = ...


1

Set up a table with the customer names as column labels across the top, and row labels with a list of possible characters down. Then use Sumproduct. Consider the following screenshot. The formula in cell B10 is =SUMPRODUCT(($B$1:$H$6=$A10)*($A$1:$A$6=B$9))


1

You were almost there with your attempt - [Table5].Rows(lrow).Interior.ColorIndex = cIndex assuming your lrow is an integer. Just adjust the table name to your table.


1

Dim excelapp As Object Set excelapp = CreateObject("Excel.Application") Is what you're looking for. If you don't see it then use excelapp.visible = true.


1

While the fix recommended by Raystafarian helped, the full solution included removing the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib{00020813-0000-0000-C000-000000000046}\1.8 I'm now a very happy camper.


1

Try this: Dim xlsPath As String xlsPath = Environ("UserProfile") & "\Desktop\Agent List for DSR.xlsx" Workbooks.Open xlsPath


1

Cells(1, 1).Formula = "=LEFT(A2;5)" using .Formula instead of .FormulaR1C1 solved the problem.


1

The change to incorporate multiple columns is quite simple. Only one line of code needs to be changed: Private Sub Worksheet_Change(ByVal Target As Range) Dim A As Range, B As Range, Inte As Range, r As Range Set A = Range("I:I,K:K,P:P") Set Inte = Intersect(A, Target) If Inte Is Nothing Then Exit Sub Application.EnableEvents = False For Each r In ...


1

Code to demonstrate how to delete the last occurrence of a given string Sub DeleteLastOccurence() Set myRange = ActiveDocument.Content myRange.Find.Execute FindText:="Hello", Forward:=False If myRange.Find.Found = True Then myRange.Delete End Sub The key part is Forward:=False where we tell VBA to search backwards Before | After Used ...


1

This code is more elegant as above user put it. But if you have 800+lines it does take a hit on the memory for some reason. Sub RemoveEmptyRows() On Error Resume Next With ActiveSheet.Range(Cells(2, 1), Cells(Rows.Count, 1)) .Value = .Value .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp End With End Sub went through one more time and ...


1

While it is true that the period available on the period select cannot be changed, you can create your own custom quarters with four buttons paired with macros. First create four macros, one for each quarter, that look like this: Sub Quarter1() ActiveWorkbook.SlicerCaches("NativeTimeline_Created_Date").TimelineState. _ SetFilterDateRange ...


1

Just to expand on the existing answer, you'll need something like this for each macro - Sub macro1() If test() Then MsgBox ("you can use macros!") 'do stuff End If End Sub You will start all macros calling test() Function test() test = False 'Pass the user to the checking function If allowed(Environ("username")) Then test = True ...



Only top voted, non community-wiki answers of a minimum length are eligible