Same VBA code for multipage 1st and 2nd page

Akbarov

Active Member
Joined
Jun 30, 2018
Messages
347
Office Version
  1. 365
Platform
  1. Windows
Hey community,

I am using same VBA code ( to search from sheet1 ) and trying to use same code for sheet2 ( on multipages 2nd page ) i am copy-pasting same code and changing sheet name on second code. but for some reason it is not working on second paage of multipage.

i am getting error : Ambiguous name detected

Code:
Private Sub TextBox1_Change()

    Dim fCell As Range, fCells As Range
    Dim Firstfound As String


    ListBox1.Clear


    With Worksheets("Nom")
        Set fCells = .Range("c1", .Range("c" & Rows.Count).End(xlUp))
    End With


    Set fCell = fCells.Find(What:=TextBox1.Value & "*", _
                            After:=fCells(fCells.Count), _
                            LookIn:=xlValues, _
                            Lookat:=xlWhole, SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, MatchCase:=False)


    If Not fCell Is Nothing Then
        Firstfound = fCell.Address
        Do
            With ListBox1
                .AddItem fCell.Value
                .List(.ListCount - 1, 1) = fCell.Value
            End With
            Set fCell = fCells.FindNext(After:=fCell)
        Loop While fCell.Address <> Firstfound
    End If


End Sub


or type does not focus error
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
What is your question?

The error is telling you have two script in the same Workbook with the same name.

And you were told to rename one of them.

Show us the two scripts.
 
Last edited:
Upvote 0
hi, i want to use same code on second page of multipage. to search and find result from different sheet


Code:
'Private Sub CommandButton1_Click()
'Dim i As Long
'    Sheets("Nomreler").Range("Z2").Value = TextBox1.Value
'        For i = 1 To 20    '<---- 20 Labels and 20 Cells in Column Z
'            Me.Controls("Label" & i + 2).Caption = Sheets("Nomreler").Cells(i, 25).Value
'        Next i
'End Sub




Private Sub CommandButton1_Click()
    Unload Me
End Sub
Code:
Private Sub ListBox1_Click()Dim i As Long
    Sheets("Nomreler").Range("Z2") = Me.ListBox1
        For i = 1 To 20
            Me.MultiPage1.Pages(0).Controls("Label" & i + 2).Caption = Sheets("Nomreler").Cells(i, "Y").Value
        Next i
End Sub






Private Sub TextBox1_Change()
    'Thanks to AlphaFrog at Mr.Excel.com 532918 Listbox displaying multiple search results as you type
    Dim fCell As Range, fCells As Range
    Dim Firstfound As String


    ListBox1.Clear


    With Worksheets("Nomreler")
        Set fCells = .Range("c1", .Range("c" & Rows.Count).End(xlUp))
    End With


    Set fCell = fCells.Find(What:=TextBox1.Value & "*", _
                            After:=fCells(fCells.Count), _
                            LookIn:=xlValues, _
                            Lookat:=xlWhole, SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, MatchCase:=False)


    If Not fCell Is Nothing Then
        Firstfound = fCell.Address
        Do
            With ListBox1
                .AddItem fCell.Value
                .List(.ListCount - 1, 1) = fCell.Value
            End With
            Set fCell = fCells.FindNext(After:=fCell)
        Loop While fCell.Address <> Firstfound
    End If


End Sub




Private Sub UserForm_Initialize()
    Sheets("Nomreler").Range("Z2").ClearContents
    TextBox1.SetFocus
End Sub
 
Upvote 0
You have two different scripts assigned to the same Button

You need to put this script in another button
Code:
Private Sub CommandButton1_Click()
    Unload Me
End Sub
 
Upvote 0
still getting same error ;/

what about
Code:
[COLOR=#333333]Private Sub UserForm_Initialize()[/COLOR]    Sheets("Nomreler").Range("Z2").ClearContents
    TextBox1.SetFocus [COLOR=#333333]End Sub[/COLOR]

new error: can't move focus
 
Last edited:
Upvote 0
I do not know what to tell you.
I always build script one step at a time if I'm not sure of myself.
And eliminate one problem before moving on.
Did you solve the ambiguous error?

If not you must solve this first.

Three of us have told you cannot have two scripts with the same name

You had two scripts with this name.

Code:
[LEFT][COLOR=#333333][FONT=monospace]'Private Sub CommandButton1_Click()
[/FONT][/COLOR][/LEFT]
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top