New answers tagged

0

Ironically you put a =True where it's not needed and forgot it where it is needed. Sub toggle() If Sheets("Navigation (2)").Visible Then ActiveWindow.SelectedSheets.Visible = False Else: Sheets("Navigation (2)").Visible = True End If End Sub As you can see in the visible method, it evaluates to TRUE or FALSE. This is why you don't need ...


0

For me it worked when I added the SetSourceData: Sub grafieken() Dim sh As Worksheet Dim chrt As Chart Dim naaaam As String naaam = ActiveWorkbook.ActiveSheet.Name Set sh = ActiveWorkbook.Worksheets(naaam) Set chrt = sh.Shapes.AddChart.Chart With chrt 'Data? .SetSourceData Source:=Range("Sheet1!$C$1:$D$11") .ChartType = xlXYScatter ...


1

this works: Sub toggle() If Sheets("Navigation (2)").Visible = True Then ActiveWindow.SelectedSheets.Visible = False Else Sheets("Navigation (2)").Visible = True End If End Sub


1

As you mentioned, you're looking for the TypeName Function. A better idea would be to use Option Explicit so all variables are dimensioned with purpose.


0

Ah, found it. Use typename(variable), where variable is the variable whose type should be returned.


1

To answer your direct question: If Selection.Areas.Count > 1 Or Selection.Cells.Count <> 5 Then More completely, we can clean up your code a bit to remove extraneous lines and add some extra checks. For instance, your will error out if the user selects a shape and then runs the code. Sub loto() 'Declarations Const minValue As Integer = ...


1

Consider: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 1 Then Exit Sub If Intersect(Target, Range("U:V")) Is Nothing Then Exit Sub Application.EnableEvents = False If Not Intersect(Target, Range("V:V")) Is Nothing Then Target.Offset(0, -1).Value = Target.Value / Range("O" & Target.Row).Value Else ...


2

If I understand correctly, you could use something like this. Private Sub Worksheet_Change(ByVal Target As Range) 'Don't apply to column headers If Target.Row = 1 Then Exit Sub 'Make changes to anything in Column V (22) If Target.Column = 22 Then If IsNumeric(Range("V" & Target.Row)) And IsNumeric(Range("O" & Target.Row)) ...


0

There are a few ambiguities in your question. This answer assumes you're talking about cell references in other workbooks, as illustrated in your examples, rather than hyperlinks, and that the task is to set up the references as adjustable by year, rather than rewrite all of the formulas each year. First, you need a year reference somewhere. It isn't ...


0

First of all, you keep talking about hyperlinks. I think what you mean are formulas that reference other files. Those are not hyperlinks. Here's the solution if what you really meant was hyperlinks: To answer your actual question: Yes, there is a macro for that. Sure. I think what you need to know about is the Worksheet.Hyperlinks object. You can loop ...


0

Can you guys just rename the macro in the shared workbook and replace the instances of that macro in that specific workbook?


1

The macro language does not have access to spreadsheet functions unless you spend several lines loading up the one you want. For the purpose here, it would be much simpler to just use the Basic concatenate operator of &. Like so: Case "0" binOut = binOut & "0000"


1

Try with the following alterations: Sub SwitchHighlightsColor() Dim r As Range Set r = Application.Selection.Range With r.Find .Highlight = True Do While .Execute(FindText:="") And r.InRange(Application.Selection.Range) If r.HighlightColorIndex = wdYellow Then ' Highlight color you want to change ...


1

You are attempting to work with the Workbook events, which are local to the object to which they belong (in this case, the add-in). What you need is an Application event. Chip Pearson has a page explaining the concept. To summarize, put this code inside the ThisWorkbook object of your add-in. Option Explicit Private WithEvents App As Application Private ...


0

You'll need to add this to the code module behind your sheet. If it's Sheet1, in VBA's Code Explorer, right click on Sheet1 and choose View Code, then add this to the module: Option Explicit Const DISPLAY_TARGET As String = "E2:E6" Const D2_SOURCE As String = "J2:J6" Const D3_SOURCE As String = "K2:K6" Private Sub Worksheet_SelectionChange(ByVal Target As ...


2

The way to view and change the mapping of keyboard shortcuts to macros in Excel is completely different than in Word. Maybe that's what's tripping you up. In Excel: In the Ribbon, click View → Macros dropdown → View Macros. That will display the "Macro" dialog. In the "Macro" dialog, click on a macro and then click the "Option" button. That will ...


0

As the error states, your formula does have too many arguments: =IF(T74="1/2Dry Stack",0.1,IF(T74="DryMortar Joint",-0.1,0,IF('Sales Agreement'!G23="Mortar Joint w/ Standard Grey",0.1,0))) I believe the actual formula you want is: =IF(T74="1/2Dry Stack",0.1,IF(T74="DryMortar Joint",-0.1,IF('Sales Agreement'!G23="Mortar Joint w/ Standard Grey",0.1,0))) ...


0

Another 'trick' - before copying the source worksheet, replace all formula qualifiers = with other set of characters (say ###=). Copy the worksheet over, then after copy, replace the formula qualifier back (replacing ###= with =). Make sure that any sheets references within the formauls are also copied to the new sheet prior to the referencing sheet.


0

This works for me: Sub ReapplyFilter() ActiveSheet.AutoFilter.ApplyFilter End Sub Hope that helps.


2

With conditional formatting: Select the row containing your data go to: Home - conditional formatting - new rule - use a formula to decide... if your data is in row 1, starting at A1, then enter this formula: =COUNTIF($A$1:A1,A1)>1 specify the condition you need (italic, font colour...) Update If you want to format multiple rows based on hear ...



Top 50 recent answers are included