Excel Form Question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
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.
 

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?
 
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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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
 
Joined
Jul 25, 2004
Messages
5,458
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?
 

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:
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top