Posts by rgrone

    Re: Search Box using Combo Box

    Yes Sorry meant ListBox and the RowSource doesn't however, I attempted this and I am now coming up with a compile error

    I'm now totally lost... I am only trying to create a form on one spreadsheet so that the user may search another spreadsheet without have to flip back and forth between spreadsheets...

    Any quick change in my code or a complete rewrite would be very helpful -- thanks

    I am trying to search one spreadsheet and return the results of the entrie row into the combobox for on partial queries many answer could be available. The data sheet contains three columns. I would like to have the results of all three columns listed in the combobox. I have attempted many variations however, with the code below, my results in the combobox only reflect column "A"----- any help would be greatly appreciated:

    Thanks ---

    Re: Running Macros on event

    Well to get through the first problem regarding the Location information incrementing and missing the value placed in the cell, I labeled the row and referenced that by referencing to that column -1 row (I am doing this this way so no one has a heart attack about the code posting)

    To solve the problem of the "copy" of the original worksheet from becoming "unprotected" I used the ActiveSheet Protect UserInterfaceOnly in the macro used to open and copy the original..... that worked just ducky...

    I tried all the rest and unfortunately the "code" posted works fine on a static workbook but since most workbooks are dynamic the line added at the end of the macro works just great!!!!!

    Thanks though : D

    Re: Protecting Sheet with VBA Code

    ActiveSheet.Protect Password:="Password", UserInterfaceOnly:=True

    Works just ducky if you enter it towards the end of your code... it also allows for one to duplicate the sheet(s) without losing control over the protection.. I've tried all the rest and they work fine for static workbooks however, in the real world workbooks are more dynamic then that...

    Good Luck


    Re: Importing data in excel

    I have been attempting to do the same thing with this exception - the peoplesoft database saves copies of records in .html unfortunately without a personal web server running importing or creating a macro to do so has become very difficult since Excel GetExternalData does not cotton to the file///c:/foo/foo2/*.html command -- any helpful ideas?

    What I have created is a scansheet that simply converts numerical input to scannable barcode - I need to protect these sheets and allow macros to run at the same time.

    I have tried both of these statements:

    With very little success - to begin the process the user is at the main menu - the "ORIGINAL" BarCode page is hidden. A Macro is written to UnHide this sheet make a copy of it and Rename it to "RENAME ME NOW" --

    The original sheet responds correctly to the above however, the copy of, no matter whether is is called BarCode (2) or RENAME ME NOW is TOTALLY Unprotected which is very bad for this group trust me..... I am surprised they can turn their alarm clocks off in the morning and I do not want to spend my entire day rebuilding corrupted worksheets.

    Now, I have even added the code to the Orignal Worksheet to look like this:

    Private Sub ProtectReNameMe()
    Sheets("RENAME ME NOW").Protect Password:="20lb8x19", _
    End Sub

    Which is appled directly to the worksheet. AM I missing a WorkSheet Open statement or some other parameter?

    HELP - I goin prematurely bald as it is and I am more familiar with Lotus 123 then Excel (Quite frankly I think Lotus is a piece of cake compared to this)


    Re: Running Macros on event

    I need to protect this worksheet so that the folks using the scan tool don't accidentally (or on purpose even) delete the code. When I protect the worksheet allowing users to edit column A and the rest locked and hidden the worksheet code cannot execute.....

    Is there a way around this?

    Re: Running Macros on event

    Thanks Tony....

    That is correct - and thanks for the help!!!

    I am kind of running into the same problem though
    When the second IF statment is exceuted the value from where it is getting the information to copy is incrementing by 1.

    Cells(Target.Row, 3).FormulaR1C1 = "=IF(RC[-2] > 1, "THIS LOCATION MUST REMAIN CONSTANT or at LEAST GET THE VALUE FROM THE CELL ABOVE","""")"

    IF you see what I mean is that value is incrementing along with the other two cells which, for the other two is just fine - My Location name will be located in Cells A1:H1 which are merged hence, this is where I would like the second if statement to retrieve it's information from...

    Thanks again for all the help.... It must be that second statement which is giving Excel as myself, grief
    : D

    Excel 2003

    I have three macros:

    However I keep getting a circular reference error --

    Any help would be greatly appreciated...

    While using a scan tool once a scan code is recorded into a cell in column A I would like to have the three macros executed

    I an trying to wirte a macro that will allow my users to import data from a HTML file locatedon their local hard drive. THe directory information will not change however I would like the opporutnity to "choose" the appropriate file prior to completing the macro.

    Do I need to construct a form first? If so, a little help would be greatly appreciated. Here's what I have:

    Sub QuestionGetExtData()
    ' QuestionGetExtData Macro
    ' Macro recorded 6/18/2006 by Robert A. Grone

    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;C:\foo\foo.html" _
    , Destination:=Range("A11"))
    .Name = "foo.html"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .Refresh BackgroundQuery:=False
    End With
    End Sub

    I would like to change the "foo" statements so that the user is prompted for this information:

    How do I change:

    "URL;C:\foo\foo.html" _
    , Destination:=Range("A11"))
    .Name = "foo.html"

    to allow for a file selection from directory c:\foo


    R Grone