How can I determine the row address of an existing xlPageBreakFull, using VBA.

    I've searched the VBA help files, the database here at OZgrid and can't find the answer. I know the code to add pagebreaks, remove them, etc. but darned if I can work out the code to determine the location of an existing page break.

    Someone, help, PLEASE



    Re: Loop through text boxes on User Form


    Once again you have saved my bacon. I had tried the me.controls statement but forgot to use the brackets. Simple solution and works great.

    Hmmmmmmmmm, now if only I could figure out how to mark this post as solved.


    Here's the scenario:

    I have a user form with 15 text boxes named form1.text1, .text2, .text3 etc.
    Values will be transferred to these text boxes, but the number of text boxes that values will be transferred to will vary. I need to create a loop that will increment the name of the text box by 1 each time the loop is run.

    I've tried every imaginable form of set statement, variable, etc. and get different kinds of error statements......"Type mismatch, With stetment not set, etc."

    Here's the loop I need:

    For trsf = 1 to num (a variable indicating the #of times loop will run) step 1
    form1.text1.value = first value
    num = num - 1
    next trsf

    The next time the loop runs, the text box name should be text1+1 or text2, etc., etc. etc., and I can't seem to figure out how to change the name so that it is recognizable as an object in the form.

    Any help would be greatly appreciated.

    Thanks in advance


    Re: Web Page opens behind User Form (SOLVED)

    Solved the problem. Sorry for the temporary brain lapse.

    The code accessing the web page was housed in a seperate procedure that was called when the command button was clicked. I had placed a statement in the command button_click setting focus to an appropriate text box. This, of course, re-activatrde the user form. Duh !!!!!!

    I moved the setfocus statement to a point before calling the web page, and Voila, no more problems.

    I have a User Form that fills the Users Screen, and on that Form I have a command button that acesses a web page. My code is:
    ActiveWorkbook.FollowHyperlink Addres "Web address...........", NewWindow:=True

    The problem I'm having is that the web page opens up behind the user form, and is therefore inaccessible to the user. If they click the command button again, the browser opens another copy of the web page, which is then visible to the user. This is annoying.

    I need the user form to stay open, as the user will obtain info from the web page, which will then be input into the user form when the web page is closed.

    I'm sure there is an easy answer to this problem, but I can't figure it out. Please help, I'm stuck!!!



    Hi ScottABZ:

    The following is code I developed (many grey hairs later!) and have used for years to disable all toolbars that are visible when Excel opens, including the menu bar. The code creates a blank menu bar. WARNING, you must establish an exit procedure, or it will be the old ctrl+alt+del!!!

    'Identify default menu bar on open
    Dim defmenbar
    defmenbar = CommandBars.ActiveMenuBar.Name
    Range("A111").Value = defmenbar 'Store name so that you can restore it on exit
    'Hide the default menu bar and create a new menu bar
    Set oldMbar = CommandBars.ActiveMenuBar
    Set newMbar = CommandBars.Add(Name:="MY MENU BAR", Position:=msoBarTop, MenuBar:=True, temporary:=True)
    With newMbar
    .Visible = True
    End With

    'Identify visible toolbars on open
    Dim bar As Object
    For Each bar In Application.CommandBars
    If bar.Visible = True And Not bar.Name = "MY MENU BAR" And Not bar.Name = CommandBars.ActiveMenuBar.Name Then
    ActiveCell.Value = bar.Name
    bar.Visible = False
    ActiveCell.Offset(1, 0).Activate
    End If
    Next bar

    'Disable right click pop up menus
    Dim cb As CommandBar
    For Each cb In CommandBars
    If cb.Type = msoBarTypePopup Then
    cb.Enabled = False
    End If
    Next cb

    CommandBars("toolbar list").Enabled = False

    The above basically "locks" the user into your program, and has worked extremely well for me for many, many years.


    Hi sp....

    You have provided very little info; however, try this:

    dayval = the date value of the field in your form

    if dayval <= Now then msgbox ("You have input an invalid date!")

    Sorry, but I don't understand your second question.


    Way to go Pesky Weasel:
    Your sentence........ 2 (maybe 3 or 4 weeks) on an exotic, tropical, deserted island, with only 1/2 ration of your favourite suds. Suffer deeply!!!

    Thanks for all your support. You have helped me solve some very complex, and frustrating problems.


    Hello pt5014:

    The following code will determine if autofilter is off or on for the particular worksheet you are working with.

    MsgBox worksheet("sheet1").AutoFilterMode

    You can probably modify the code to determine if it has been set for a particular range, although I haven't played with that.

    Good luck


    Hi Golf4:

    Please forgive me for piggybacking on your request. I'm interested in playing around with the speach possibilities and also use Excel 2000 with Windows 2000.

    My reference list does not include Microsoft Speach Object Library, although it does include Speach Recognition, Text to voice, etc.

    Does anybody know how I go about obtaining the Microsoft Speach Object Library without upgrading my operating system to XP?

    Thanks in advance


    Hi Aaron:

    I was in your neck of the woods last week, at least I think Orlando is close to Ft. Lauderdale. My only question:

    What are you doing going to a hockey game??? You should be going to the beach! We're still getting snow in the mountains around here! (Yuck)


    ps to make this a legit forum post, can you please tell me what API stands for. Thanks

    Hi Derk:
    I had a similar problem when I needed to create a macro to hide the desktop taskbar. To make Excel the active application after hiding the task bar, I used the following:

    Public Sub HideTaskBar()
    'A bunch of my code was placed here

    'Last statement in the sub
    AppActivate ("Microsoft excel")

    End Sub

    A simple solution that worked for me, and hopefully will work for you as well.


    Thanks Neale, Bnix, and XL-Dennis for all of your help. You have provided me with a lot of "food for thought".

    Dennis, when you suggest that I must have access to MDAC, are you referring to MDAC in the VBA Editor (Tools......References)??

    Thanks again


    Hi All:

    The wisdom of the database wizards of this site is required and would be greatly appreciated.

    For years and years I have been merrily using Excel and automating my applications through the use of VBA. This has served my users well.

    Some of my creations are now being used on network and shared drives by many users. As you can imagine, this is causing problems, particularly with sequential input, changes, etc.

    I'm thinking that I should now use Excel as a user interface only, and create future databases using Access, or whatever is the easiest dbase to learn. Here's where I need your help:
    1) Is it easy to input/extract data from Access using VBA within Excel?
    2) Does the user have to purchase expensive Access software from Sir Gates or is the basic Access dbase software available as a free download?
    3) I believe Outlook uses Access to store info, and I use Outlook; however, have never purchased or installed Access. Does Outlook use Access?
    4) Any suggestions as to how I can get up to speed real quick using Access through Excel.
    5) I notice software advertised on this site: "CodeBase for Excel". Has anybody used this software? Wish to share your experiences with this particular software, god, bad or otherwise?

    My primary objective is to continue using Excel, as I am able to develop "user friendly & bullet proof interfaces" in this platform. I do; however, see the need to start using a more sophisticated database program.

    Any Recommendations and suggestions would be most welcome.



    Hi Epidemic:
    Hope you don't mind if I piggyback on your request, as I am also interested in responses you may receive.

    I have a similar situation with a program that runs perfectly well in W98 & W2000. Values in text boxes are interpreted as numeric in W98 & W2000 with no problem; however, not so in XP.



    Hi Brandtrock:

    I've been using Setup2Go to create my auto run CD's for a few months. It does an excellent (and very professional) job, and you don't have to be a rocket scientist to learn how to use it. Best of all, it's relatively inexpensive ($29 US).

    Have a look at Well worth the money!!!


    Hi Jen:

    You may wish to re-organize the columns so that the three hidden columns are not the first (or last columns) in the table. If there are columns of data on either side, I believe they will sort with the table, even if they are hidden.

    Your 3 suggestions also have merit, but will require some pretty fancy coding. I haven't played around with command bars that much, so I am unable to provide you with a start.

    You may wish to delete the sort function from the Data menu, create a new function and then assign the appropriate code.

    Perhaps, some of the experts on this site will be good enough to give you some coding to get you started.


    Thanks very much Andy and Richie. The code you have provided is perfect!!

    I develop Excel applications that are used on a variety of machines. I prefer to work with the screen resolution set to 800x600, but many don't. It has been a long, perplexing problem of how to deal with this.

    The code you have provided will allow me to automate the task of adjusting my applications to the users display settings.

    Thanks again, you are both superstars in my books!!


    Is there a simple way to determine the display settings (800x600; 1024x768, etc.) on a host machine using VBA in Excel??

    I've searched the Microsoft help files and this site for an answer, to no avail.

    If this is possible, would appreciate the code.

    Many thanks