1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Macro for User Form

Discussion in 'Business Applications' started by runner77, Oct 25, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. runner77

    runner77 Thread Starter

    Joined:
    Jan 11, 2006
    Messages:
    42
    Hi Guys,

    I have a marina map I have set up in excel, which has a number of berths on it.

    I would like a user form ( already set up) once I have clicked on any berth on the map sheet to display the Berth No, Berth Length and the Boat Name from the Database sheet.



    Thanks in Advance
    Runner77
     

    Attached Files:

  2. runner77

    runner77 Thread Starter

    Joined:
    Jan 11, 2006
    Messages:
    42
    Hi There been trying this code form opens but does not display the information or close. What am I doing wrong??? I will need Separate macros for each of the 50 berths I want to dsiplay with this user form.



    Code:
    Sub PPMB1()
        UserForm1.Show
        TextBox1 = Worksheets("Database").Range("A3")
        TextBox2 = Worksheets("Database").Range("B3")
        TextBox3 = Worksheets("Database").Range("S3")
    End Sub
        
     Private Sub CmdExit_Click()
        Unload UserForm1
        End Sub
     
  3. runner77

    runner77 Thread Starter

    Joined:
    Jan 11, 2006
    Messages:
    42
    Have changed the code to below But it only works for PPMB19-PPMB34. Would love someones help to get this to work.

    Code:
    Private Sub UserForm_Initialize()
    Berth = ActiveSheet.Shapes(Application.Caller).Name
    Me.TextBox1.Text = WorksheetFunction.VLookup(Berth, Worksheets("Database").Range("A1:Z36"), 1)
    Me.TextBox2.Text = WorksheetFunction.VLookup(Berth, Worksheets("Database").Range("A1:Z36"), 2)
    Me.TextBox3.Text = WorksheetFunction.VLookup(Berth, Worksheets("Database").Range("A1:Z36"), 19)
    
    End Sub
    
    Private Sub CmdExit_Click()
        Unload UserForm1
        End Sub
      
    Thanks
    Runner77
     

    Attached Files:

  4. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    This is because the Lookup range is not the same as the real rage you have your data in. That's why you need to update your lookup range.

    Change your code to this:
    Code:
    Private Sub UserForm_Initialize()
        Dim dBase As Range
        
        Berth = ActiveSheet.Shapes(Application.Caller).Name
        Set dBase = Worksheets("Database").Range("A1:Z236")
        
        Me.TextBox1.Text = WorksheetFunction.VLookup(Berth, dBase, 1)
        Me.TextBox2.Text = WorksheetFunction.VLookup(Berth, dBase, 2)
        Me.TextBox3.Text = WorksheetFunction.VLookup(Berth, dBase, 19)
    
    End Sub
    
    Private Sub CmdExit_Click()
        Unload Me
    End Sub
     
  5. runner77

    runner77 Thread Starter

    Joined:
    Jan 11, 2006
    Messages:
    42
    Thanks AJ

    Cheers
    Runner
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/871660

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice