• WSrory



    Viewing 15 replies - 1 through 15 (of 5,753 total)
    • in reply to: Speed up the Worksheet_Change #1594080

      The first part puts up a message but does not exit the routine, so the duplicate check is then done again anyway. It also monitors columns A:G even though many of them are irrelevant to the duplicate check.

      I think it would be useful to have – in words, not code – an explanation of exactly what should happen.

    • in reply to: Chart Expert Needed re Bar Chart Colors #1593850

      Here’s a simple example, based on your workbook layout that colours each bar to the same as each cell’s fill colour:

          Dim dataRange As Range
          Dim n As Long
          Set dataRange = Range("A2:A5")
          For n = 1 To dataRange.Cells.Count
              ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(n).Format.Fill.ForeColor.RGB = _
          Next n
    • in reply to: Speed up the Worksheet_Change #1593838

      It looks to me as though the first two sections are essentially doing the same thing – i.e. checking for duplicates – so there isn’t much point in doing it twice.

    • in reply to: Chart Expert Needed re Bar Chart Colors #1593837

      If you wish for each sales person to have a different color then the graph needs to be set up where each sales person is a series.

      You can have different colours for individual points in a bar chart, so you don’t have to use separate series.

    • in reply to: Conditional Formatting #1592849

      I must be missing something here as I don’t actually see the problem. If you simply change the formatting rules that currently make the cell green so that they don’t apply a fill but change the font instead, you don’t have to worry about the cell fill.

    • in reply to: Formula column in MS Query #1592257

      What data source are you extracting from?

    • in reply to: Error Message when selecting options > Formulas #1591477

      Check the location specified in the Save section of Options. It appears to be invalid.

    • in reply to: Count non-contiguous cells that are not empty #1591222

      You need a couple of additional coercions. First you need N() – or something equivalent – to force an array of values to be returned. Second you need to coerce the True/False to 1/0. So something like this should work:


    • in reply to: Count non-contiguous cells that are not empty #1591213

      If the cells are either blank or have a number, using COUNT would be simpler (unless they can contain 0 and you don’t want to count those).

      Otherwise, you could use OFFSET:


    • in reply to: VBA in Office 2016 vs Office 2003 #1590066

      Same problem. An ADODB.Field doesn’t have a Size property. You need to declare it as DAO.Field

    • in reply to: Find First Formula In a Range that Evaluates to a Number #1590065

      If you add an additional INDEX to Maudibe’s approach, this doesn’t require array-entry:


    • in reply to: VBA in Office 2016 vs Office 2003 #1589241

      I’d say your problem is that you haven’t specified the Recordset type and your ADO reference precedes your DAO one. An ADO recordset doesn’t have an Edit method. Declare your rst variable as DAO.Recordset

    • in reply to: Need application to stop if file exists #1589233

      You could perhaps use this version of your original:

      Sub ExportCSVFINAL()
          Dim wbkExport             As Workbook
          Dim shtToExport           As Worksheet
          fname = Cells(1, 15).Value
          Set shtToExport = ThisWorkbook.Worksheets("MarrReformatted")     'Sheet to export as CSV
          Set wbkExport = Application.Workbooks.Add
          shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
          'check if file exists
          If Dir(fname)  vbNullString Then
              ' prompt to overwrite
              If MsgBox(Prompt:="File already exists. Do you want to overwrite?", _
                          Buttons:=vbYesNo + vbQuestion, Title:="Overwrite file?") = vbNo Then
                  ' exit if no
                  Exit Sub
                  ' delete the file if yes
                  Kill fname
              End If
          End If
          wbkExport.SaveAs Filename:=fname, FileFormat:=xlCSV
          wbkExport.Close SaveChanges:=True
      End Sub
    • I would guess it’s some code that is running automatically, either in an add-in or in your personal macro workbook. If you hold Ctrl down while opening Excel and answer Yes to open it in safe mode, then close it down, do you still see the process left running?

    • in reply to: Excel 2016 Toolbars #1588968

      If you want to add macro buttons to a toolbar, you can either add them to the Ribbon or to the Quick Access Toolbar (QAT) that sits either just above or just below the ribbon. You can customise both through the Excel Options dialog (File – Options)

    Viewing 15 replies - 1 through 15 (of 5,753 total)