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.

Excel Form Question

Discussion in 'Business Applications' started by Shaun_email, Feb 1, 2007.

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

    Shaun_email Thread Starter

    Joined:
    Feb 1, 2007
    Messages:
    3
    I have a Excel 2003 spreadsheet which contains lot's of columns. This spreadsheet view by many people but only I edit it. I created a userform which neatly displays all the columns, in a Excel form view, and allows the viewer to move up/down the spreadsheet's fields. My problem is trying to get either a button or selectable box which would let the viewer select from one the 3 different worksheets in the workbook, all while staying in the userform. I don't want the viewers to ever see the spreadsheet. The 3 worksheets all have the exact same layout. I've seen some macro examples that I inserted in the VB code has a module, but they all pull up a dialog box in the spreadsheet view, and I want the viewers to view the data within the userform I created. Any ideas/code example would be greatly appreciated.

    Thanks.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi, welcome to the board. :)

    Re: "they all pull up a dialog box in the spreadsheet view" -- use a command button on a blank sheet to run the form (& hide the data sheets).

    Re: "select from one the 3 different worksheets in the workbook, all while staying in the userform" -- see VBA Help for "MultiPage Control".

    HTH.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Bomb, could the op use a Combo or list box on the Form with the 3 sheet names in and use that to activate the switch?
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    No reason why not. The only reservation I'd have with that would be the size of the form -- if there are very many fields, it could be pretty big already.
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I've seen, and developed, many forms which have a combo box and allow the user to select all open workbooks (or you can set the criteria for the workbooks which are shown in the combo box) and perform the operations on it. The workbook can be set to a variable very easily from the combo box, especially if it is used on only the open workbooks. Keep in mind that this works best on a modal userform, modeless could cause problems.

    Shaun_Email, if you would like more detailed help, posting an example of your form and detailing out what exactly you would like the operations to do would help.
     
  6. Shaun_email

    Shaun_email Thread Starter

    Joined:
    Feb 1, 2007
    Messages:
    3
    Hi, and Thanks for all the positive imput. I was able to figure out how to code option buttons which when chosen selects one of the 3 worksheets in the Excel file. But for some reason when in the userform and switching worksheets the 1st single word from the previous sheet's cell is replaces the 1st word in the next worksheet whenever I use my command buttons to toggle thru the Excel fields. This was never the case before I added the VB code which would let the user select, and view another worksheet from my single form. Any ideas?


    Dim lCurrentRow As Long
    Sub Auto_Open()
    frmMain.Show
    End Sub
    Private Sub cmdPrev_Click()
    ' Show previous only if not already in first row:
    If lCurrentRow > 2 Then
    ' Save form contents before changing rows:
    SaveRow
    ' Decrement row number:
    lCurrentRow = lCurrentRow - 2
    ' Show contents of row in the form:
    LoadRow
    End If
    End Sub

    Private Sub cmdNext_Click()
    ' Save form contents before changing rows:
    SaveRow
    ' Increment row number:
    lCurrentRow = lCurrentRow + 2
    ' Show contents of row in the form:
    LoadRow
    End Sub

    Private Sub cmdDelete_Click()
    Dim smessage As String
    smessage = "Are you sure you want to delete " + txtName.Text + "?"
    If MsgBox(smessage, vbQuestion + vbYesNo, _
    "Confirm Delete") = vbYes Then
    ' Delete current row
    Rows(lCurrentRow).Delete
    ' Show contents of new current row in the form:
    LoadRow
    End If

    End Sub

    Private Sub cmdAdd_Click()
    ' Save form contents before changing rows:
    SaveRow

    ' Set current row to first empty row, i.e. one row after
    ' the last row currently in use:
    If Cells(1, 1).Value = "" Then
    lCurrentRow = 1 ' (list is empty - start in row 1)
    Else
    lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1
    End If

    ' Clear the form for user to add new name:
    LoadRow

    ' Set focus to Name textbox:
    txtName.SetFocus
    End Sub

    Private Sub Label4_Click()

    End Sub

    Private Sub Label10_Click()

    End Sub

    Private Sub UserForm_Activate()
    ' Read initial values from Row 2:
    lCurrentRow = 2
    LoadRow
    End Sub

    Private Sub cmdClose_Click()
    ' Save form contents before closing:
    SaveRow
    Unload Me ' Close the form
    End Sub

    Private Sub LoadRow()
    txtOfficeLocation.Text = Cells(lCurrentRow, 1).Value
    txtDCLocation.Text = Cells(lCurrentRow, 2).Value
    txISContact.Text = Cells(lCurrentRow, 3).Value
    txDeviceType.Text = Cells(lCurrentRow, 4).Value
    txCliplbk.Text = Cells(lCurrentRow, 5).Value
    txLanIP.Text = Cells(lCurrentRow, 6).Value
    txWanIP.Text = Cells(lCurrentRow, 7).Value
    txNetCarrier.Text = Cells(lCurrentRow, 8).Value
    txNetSpeed.Text = Cells(lCurrentRow, 9).Value
    txLocalRemote.Text = Cells(lCurrentRow, 10).Value
    txCircuitID.Text = Cells(lCurrentRow, 11).Value
    txCarrierContact.Text = Cells(lCurrentRow, 12).Value
    End Sub

    Private Sub SaveRow()
    Cells(lCurrentRow, 1).Value = txtOfficeLocation
    Cells(lCurrentRow, 2).Value = txtDCLocation
    Cells(lCurrentRow, 3).Value = txISContact
    Cells(lCurrentRow, 4).Value = txDeviceType
    Cells(lCurrentRow, 5).Value = txCliplbk
    Cells(lCurrentRow, 6).Value = txLanIP
    Cells(lCurrentRow, 7).Value = txWanIP
    Cells(lCurrentRow, 8).Value = txNetCarrier
    Cells(lCurrentRow, 9).Value = txNetSpeed
    Cells(lCurrentRow, 10).Value = txLocalRemote
    Cells(lCurrentRow, 11).Value = txCircuitID
    Cells(lCurrentRow, 12).Value = txCarrierContact
    End Sub

    Private Sub WANInventory_initialize()
    frmWANInventory.Show
    End Sub 'This shows the form

    Private Sub vclientsprdsheet_Click()
    Sheets("Client Network").Select
    Load WANInventory
    End Sub 'This button is on page1 of the multipage control

    Private Sub vdomesticsprdsheet_Click()
    Sheets("Domestic Network").Select
    Load WANInventory
    End Sub 'This button is on page2 of the multipage control

    Private Sub vinternationalsprdsheet_Click()
    Sheets("International Network").Select
    Load WANInventory
    End Sub 'This button is on page3 of the multipage control
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I could wade through your code but I'm not going to. This doesn't mean much to me without knowing which controls do what (as intended). One thing I do notice is that you do not reference any worksheets in your code. If your incriment numbers are for the worksheets, use that number as the index number for the worksheets object. Or, if you have a modeless userform, use the activesheet object. It's always best to explicitly define what you are doing and when.

    Are you able to upload a sample of your file?
     
  8. Shaun_email

    Shaun_email Thread Starter

    Joined:
    Feb 1, 2007
    Messages:
    3
    I've attached a copy of the Excel userform I've been trying to create, and has I stated in my last posting when switching between worksheets the 1st single word from the previous sheet's cell is replaces the 1st word in the next worksheet whenever I use my command buttons to toggle thru current worksheets fields while in the Excel userform. Thank's for everyone's assistance helping me along with my limited knowledge of Excel's VBA language.:confused:
     

    Attached Files:

  9. 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/540301

  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