Posts by mmmr

    Thanks Tom. I'll have a look at it later. I have encountered another problem with my solution since. Whilst it navigates to the bookmark fine, it's leaving it at the bottom of the visual area (ie the bookmark may be the top of the page but the bottom area of the previous page is showing mainly), so I'm currently looking for a way to find the bookmark and then scroll the bookmark to the top of the visual area.

    Finally got it working. It was a double quote issue. These do tend to mash my mind a bit. Here is the solution

    wrdApp.ActiveDocument.Bookmarks("" & bmRef & "").Select

    Hi. Thanks for that but I still can't get it to work. Here are the three bits of code I currently have in the Form code

    Public bmRef As String

    Should this be outside of the form code?

    2nd bit

    Private Sub CMB_01_DFTR_Click()
        bmRef = "A02"
        Call openword
    End Sub

    3rd bit

    Hi. I have some buttons on a userform which all relate to bookmarks in a word document. The code I have works fine. Here it is.

    However, there is a potential for there to be hundreds and hundreds of bookmarks in the document. Each of these will require buttons on a multipage form. What I'm trying to do is on clicking the button enter the name of the bookmark ( in the case above "A02") and then send it to the rest of the code as a sub. The string name I'm going with at the moment is bmRef (ie bookmark reference). So the code should look something like this. However this code below would now be a subroutine the button only sending bmRef to the public variable

    The problem I'm having is that I get an error whenever I run this.
    I have declared bmRef as a public string but I can't seem to get it to work. Any ideas?

    Thanks. From all my google searches it seems unlikely that there's an obvious solution. I was hoping not to have to create multiple sheets or pages. I'm wondering if it's something maybe better suited to C# or something.

    I have some code to print from a loop and this works fine apart from it's making a new document from each loop. What I want is to be able to print all pages to a single document. Here is the code I have

    I'm printing to a pdf creator btw so that I can check for errors before printing to paper

    I've amended your function slightly to return the colour of the non-gradient cells too. Here is my amended code

    Function Grad(rng As Range) As String
      If rng.Interior.Pattern = 4000 Then
        Grad = rng.Interior.Gradient.ColorStops(1).Color & "   -   " & rng.Interior.Gradient.ColorStops(2).Color
        Grad = rng.Interior.ColorIndex
      End If
    End Function

    Thankyou so much. That does everything I was looking for.

    That's what I want to do but I've been using this spreadsheet for 6 + years and so have the formats already set and so I'd like to find a way to extract the formatting details for each record so as to keep them the same. Ideally I'd like to use something to get the results from the paintbrush icon if that was at all possible. I did find some code to extract each gradient point so it's possible I could do it that way. However, my curiosity has now got the better of me so I'm hoping to find a way to extract the formatting somehow

    Hi. I've attached a simplified version of the sheet I'm working on with some comments in the code from the enter button on the form. It's a bit off topic from what I started this post about but it shows what I was trying to do. Incidentally, if there is a solution to what I've posted I'd be interested to know it as I dislike to not be able to find solutions to things.

    Thanks XenoCode. I'm not entirely sure how to use this but I'll do a google search and see if I can muster some understanding. I seem to have gone off on a complete tangent to what I was trying to do.
    Essentially, I have a spreadsheet with a list of books, authors and project associations along with dates etc... and most of the book names are formatted. Some are formatted with a single colour and others are formatted with gradients. Essentially, what I want to do is store the properties of the cells formatting, as I would if I clicked the Format painter. Instead of pasting the format in though, I'd like to store the properties of the formatting of the cell so that I can do a lookup based on the value of a combobox. A bit like conditional formatting if you see what I mean. I know this is a bit off topic with the thread but I just figured it probably made sense for me to explain what I'm up to. I've found a way of extracting the colouring information from a cell without the gradient, and I've found a way of extracting the information from gradients but it won't let me differentiate between the two, which is why I was looking for a way to check the gradient status.
    Thanks so much for your time and patience with me on this matter.

    I'm getting a run-time error '438' Object doesn't support this property or method when I use either

    If ActiveCell.Fill.GradientColorType = LinearGradient Then
    MsgBox "Two Colors"
      End If


    If ActiveCell.Fill.LinearGradient = True Then
    MsgBox "Two Colors"
      End If

    I've not posted code because I've spent 2 hours and gotten nowhere. I've experimented with things such as TwoColorGradient Things such as


    ActiveChart.SeriesCollection(1).Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=1

    I've found on google as well as


    .OneColorGradient c1f.GradientStyle, _ c1f.GradientVariant, c1f.GradientDegree

    . I was just hoping there would be something a bit more obvious.
    My code would look something like

    If ActiveCell.Fill.GradientColorType = msoGradientTwoColors Then
    MsgBox "Two Colors"
      End If

    where I'd put my what to do with it if it's true stuff.
    Obviously in the examples above I've adapted them to Range("A" & Activecell.row)
    I shall try the LinearGradient you've mentioned later on when I have some time.
    Sorry my post was so brief it's just I have no idea where to start

    Hi. In one column I have a list of books. The backgrounds are sometimes a single colour and sometimes have gradient fills. The fills have been done by right clicking the cell, selecting format cell and fill effects and then using a two colour gradient (labelled as Two colors). Is there a way to check if the cell is an ordinary background colour or a two coloured one? The reason for this is that I designate the books to various projects and usually copy/paste the format from a previously formatted cell but I'm trying to automate the colouring from pressing enter on a userform based on a combobox value. I have already found a way to extract the two colours from those cells that have the two colour gradient but I'm struggling to find a way to differentiate between the two formatting types as I have been getting "object variable not set" errors on the non gradient cells..

    Thanks Kenneth. I do comment out a version in the code so that I can work through it step by step to get the R1C1 from it. I just failed miserably with it when I tried earlier.
    Jolivanes that was a brilliant idea. I wish I'd thought of it myself. It worked perfectly.
    Thankyou both for your replies they were very helpful.

    Range("J" & newrow).FormulaR1C1 = "=IF(RC[-6] ="""",0),If(RC[-5]="""",Today()-RC[-6],RC[-5]-RC[-6]))"[B]

    I'm trying to use the above code to run from a command button on a userform but I keep on getting errors returned



    is what should be entered.
    What am I doing wrong?

    It is because I can't get sendkeys to work how I want it to on this occasion that I've tried using API instead. Sorry for any confusion. I'm completely new to the API thing

    Thanks. I've used API a few times on other programs with sendkeys but sendkeys seems incredibly unreliable in this particular program as it was selecting a different button every time I ran the macro.

    Hi. I want to use Excel VBA to enter values into a metronome program. The program is called Presto Metronome and it has a training function that I use regularly. The problem is that the presets don't save in it. Therefore I came up with the idea of storing the variables and entering them automatically from VBA. For example. One exercise may start at 90 bpm and proceed to 120 bpm over 5 minutes with an accent of 3 and another may start at 70 bpm going to 80 bpm over 10 minutes accent 4. I have used Spy++ to find the button handle to take me to the presets screen but can't find how to activate it from Excel VBA. Ideally I'd like to be able to go to the second page and enter the values. The window Handle of the button is 00340818. Any ideas? This is the button that takes me to the training settings page.

    Thankyou for that. I couldn't get it to do what I wanted but you gave me a solid starting point for what I was trying to achieve. I've now got it working using the code

    val25c45 = Application.WorksheetFunction.CountIfs(Sheets("Archive").Range("A:A"), _
    ">=" & StDt, Sheets("Archive").Range("A:A"), "<=" & EndDt, Sheets("Archive").Range("B:B"), "Example 1")

    I've attached the updated dummy sheet in case you were interested in the full thing