Posts by rgrone

Re: Searching through a worksheet for text or value entered in a text box

How would you be apple to select and display the entire row range of the search item located?

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:

Option Explicit 


Sub Locate(Name As String, Data As Range) 


    Dim rngFind As Range 
    Dim strFirstFind As String 
     
    With Data 
        Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart) 
        If Not rngFind Is Nothing Then 
            strFirstFind = rngFind.Address 
            Do 
                If rngFind.Row > 1 Then 
                    ListBox1.AddItem rngFind.Value 
                    ListBox1.AddItem rngFind.Row 
                    ListBox1.List(ListBox1.ListCount - 2, 3) = rngFind.Value & "!" & rngFind.Row 
                End If 
                Set rngFind = .FindNext(rngFind) 
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind 
        End If 
    End With 
     
End Sub 


Private Sub CommandButton1_Click() 


    Dim shtSearch As Worksheet 
     
    ListBox1.Clear 
    For Each shtSearch In ThisWorkbook.Worksheets 
        Locate TextBox1.Text, shtSearch.Range("A:C") 
    Next 
    If ListBox1.ListCount = 0 Then 
        ListBox1.AddItem "No Match Found" 
        ListBox1.List(2, 3) = "" 
        ListBox1.List(2, 2) = "" 
    End If 
End Sub 


Private Sub ListBox1_Click() 


End Sub 




Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 
     
    Dim strSheet As String 
    Dim strAddress As String 
    Dim strRow As String 
    strRow = Application.Union(rRow, rCell) 
    strSheet = ListBox1.List(ListBox1.ListIndex, 2) 
    strAddress = ListBox1.List(ListBox1.ListIndex, 3) 
    If strAddress <> "" Then 
       Range(strAddress).Activate 
    End If 
End Sub 




Private Sub UserForm_Click() 


End Sub

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

pirate:

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:

Private Sub Workbook_Open()
Dim wSheet As Worksheet
 
    For Each wSheet In Worksheets
        wSheet.Protect Password:="Secret", _
            UserInterFaceOnly:=True
    Next wSheet
End Sub
 
------and------
 
Sub MyMacro()
 
    Sheet1.Unprotect Password:="Secret"
    'YOUR CODE
    Sheet1.Protect Password:="Secret"
End Sub

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", _
UserInterFaceOnly:=True
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)

:confused:

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:

Sub Concat()
 
ActiveCell.FormulaR1C1 = "=IF(RC[-1] > 1,CONCATENATE(""*"",RC[-1],""*""),"""")"
 
End Sub
Sub InsertLocation()
 
ActiveCell.FormulaR1C1 = "=IF(RC[-2] > 1, R[2]C[2],"""")"
 
End Sub
 
Sub Dupes()
'
ActiveCell.FormulaR1C1 = "=IF(RC[-3] > 1, COUNTIF(C[-3],RC[-3]),"""")"
 
End Sub
 
I need to have them excecute automatically once data is enter into a cell in column A:
 
I've attempted onEvent configurations starting with -
 
Sub FillScanSheet()
ActiveCell.EntireColumn.Select
If ActiveCell > 1 Then
Application.Run Concat
Application.Run InsertLocation
Application.Run Dupes
End IF

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

Re: Get External Data Web Query from local directory

Quote from norie

Use GetOpenFilename.

I do not understand...

With ActiveSheet.QueryTables.Add(Connection:= _
GetFileOpen."C:\foo"
, Destination:=Range("A1"))

Is this what you are indicating?

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
'

'
Range("H11").Select
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

Thanks....

R Grone
:confused: