User form needs to put data on a Hidden Sheet...

M

menor59

Well-known Member
Joined
Oct 3, 2008
Messages
574
Office Version
  1. 2021
Platform
  1. Windows
When i run this and input data it works wonderfully if its unhidden...I need to Hide the ProWatch Data Sheet and it needs to still work with that sheet hidden...

Code: 
Dim currentrow As Long


Private Sub cmdGetNext_Click()
Application.ScreenUpdating = False
Sheets("ProWatch Data").Select
Range("A2").Select
ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row


currentrow = currentrow + 1
If currentrow = lastrow + 1 Then
currentrow = lastrow
MsgBox "You have reached the Last Row"
End If
txtReader00.Text = Cells(currentrow, 1).Value
txtReader01.Text = Cells(currentrow, 2).Value
txtInput1.Text = Cells(currentrow, 3).Value
txtInput2.Text = Cells(currentrow, 4).Value
txtInput3.Text = Cells(currentrow, 5).Value
txtInput4.Text = Cells(currentrow, 6).Value
txtOutput1.Text = Cells(currentrow, 7).Value
txtOutput2.Text = Cells(currentrow, 8).Value
txtOutput3.Text = Cells(currentrow, 9).Value
txtOutput4.Text = Cells(currentrow, 10).Value
Application.ScreenUpdating = True
End Sub


Private Sub CmdPrevBefore_Click()
currentrow = currentrow - 1
If currentrow > 1 Then
txtReader00.Text = Cells(currentrow, 1).Value
txtReader01.Text = Cells(currentrow, 2).Value
txtInput1.Text = Cells(currentrow, 3).Value
txtInput2.Text = Cells(currentrow, 4).Value
txtInput3.Text = Cells(currentrow, 5).Value
txtInput4.Text = Cells(currentrow, 6).Value
txtOutput1.Text = Cells(currentrow, 7).Value
txtOutput2.Text = Cells(currentrow, 8).Value
txtOutput3.Text = Cells(currentrow, 9).Value
txtOutput4.Text = Cells(currentrow, 10).Value


ElseIf currentrow = 1 Then
MsgBox "This is your first Record"
currentrow = currentrow + 1
End If


End Sub


Private Sub cmdSend_Click()
Application.ScreenUpdating = False
Sheets("ProWatch Data").Select
Range("F4").Select
ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row
' MsgBox lastrow
Cells(lastrow + 1, 1).Value = txtReader00.Text
Cells(lastrow + 1, 2).Value = txtReader01.Text
Cells(lastrow + 1, 3).Value = txtInput1.Text
Cells(lastrow + 1, 4).Value = txtInput2.Text
Cells(lastrow + 1, 5).Value = txtInput3.Text
Cells(lastrow + 1, 6).Value = txtInput4.Text
Cells(lastrow + 1, 7).Value = txtOutput1.Text
Cells(lastrow + 1, 8).Value = txtOutput2.Text
Cells(lastrow + 1, 9).Value = txtOutput3.Text
Cells(lastrow + 1, 10).Value = txtOutput4.Text
Range("A2").Select
txtReader00.Text = ""
txtReader01.Text = ""
txtInput1.Text = ""
txtInput2.Text = ""
txtInput3.Text = ""
txtInput4.Text = ""
txtOutput1.Text = ""
txtOutput2.Text = ""
txtOutput3.Text = ""
txtOutput4.Text = ""
Application.ScreenUpdating = True
End Sub




Private Sub UserForm_initialize()
currentrow = 1
If currentrow = 1 Then
MsgBox "You are now in the header row. Click Next to see the first data"
txtReader00.Text = ""
txtReader01.Text = ""
txtInput1.Text = ""
txtInput2.Text = ""
txtInput3.Text = ""
txtInput4.Text = ""
txtOutput1.Text = ""
txtOutput2.Text = ""
txtOutput3.Text = ""
txtOutput4.Text = ""
End If
End Sub

Thoughts or comments please.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Try following:


Code: 
Dim currentrow As Long


Private Sub cmdGetNext_Click()
    Dim wsProWatchData As Worksheet
    Dim LastRow As Long
    
    Set wsProWatchData = ThisWorkbook.Worksheets("ProWatch Data")
    
    LastRow = wsProWatchData.Cells(wsProWatchData.Rows.Count, "A").End(xlUp).Row
    
    currentrow = currentrow + 1
    
    If currentrow = LastRow + 1 Then
    currentrow = LastRow
    MsgBox "You have reached the Last Row", 48, "End Of File"
    End If
    
    With wsProWatchData


        txtReader00.Text = .Cells(currentrow, 1).Value
        txtReader01.Text = .Cells(currentrow, 2).Value
        txtInput1.Text = .Cells(currentrow, 3).Value
        txtInput2.Text = .Cells(currentrow, 4).Value
        txtInput3.Text = .Cells(currentrow, 5).Value
        txtInput4.Text = .Cells(currentrow, 6).Value
        txtOutput1.Text = .Cells(currentrow, 7).Value
        txtOutput2.Text = .Cells(currentrow, 8).Value
        txtOutput3.Text = .Cells(currentrow, 9).Value
        txtOutput4.Text = .Cells(currentrow, 10).Value
    End With
End Sub

Dave
 
Last edited:
Upvote 0
dmt32 said:
Try following:


Code: 
Dim currentrow As Long


Private Sub cmdGetNext_Click()
    Dim wsProWatchData As Worksheet
    Dim LastRow As Long
    
    Set wsProWatchData = ThisWorkbook.Worksheets("ProWatch Data")
    
    LastRow = wsProWatchData.Cells(wsProWatchData.Rows.Count, "A").End(xlUp).Row
    
    currentrow = currentrow + 1
    
    If currentrow = LastRow + 1 Then
    currentrow = LastRow
    MsgBox "You have reached the Last Row", 48, "End Of File"
    End If
    
    With wsProWatchData


        txtReader00.Text = .Cells(currentrow, 1).Value
        txtReader01.Text = .Cells(currentrow, 2).Value
        txtInput1.Text = .Cells(currentrow, 3).Value
        txtInput2.Text = .Cells(currentrow, 4).Value
        txtInput3.Text = .Cells(currentrow, 5).Value
        txtInput4.Text = .Cells(currentrow, 6).Value
        txtOutput1.Text = .Cells(currentrow, 7).Value
        txtOutput2.Text = .Cells(currentrow, 8).Value
        txtOutput3.Text = .Cells(currentrow, 9).Value
        txtOutput4.Text = .Cells(currentrow, 10).Value
    End With
End Sub

Dave
Click to expand...

Hi Dave ...No Go...

I did the following...can you stream line it??

Code: 
Dim currentrow As Long


Private Sub cmdGetNext_Click()
Application.ScreenUpdating = False
Sheets("ProWatch Data").Visible = True
Sheets("ProWatch Data").Select
Range("A2").Select
ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row


currentrow = currentrow + 1
If currentrow = lastrow + 1 Then
currentrow = lastrow
MsgBox "You have reached the Last Row"
End If
txtReader00.Text = Cells(currentrow, 1).Value
txtReader01.Text = Cells(currentrow, 2).Value
txtInput1.Text = Cells(currentrow, 3).Value
txtInput2.Text = Cells(currentrow, 4).Value
txtInput3.Text = Cells(currentrow, 5).Value
txtInput4.Text = Cells(currentrow, 6).Value
txtOutput1.Text = Cells(currentrow, 7).Value
txtOutput2.Text = Cells(currentrow, 8).Value
txtOutput3.Text = Cells(currentrow, 9).Value
txtOutput4.Text = Cells(currentrow, 10).Value
Sheets("ProWatch Data").Visible = False
Sheets("Info_Sheet").Select
Range("A2").Select
Application.ScreenUpdating = True
End Sub


Private Sub CmdPrevBefore_Click()
Application.ScreenUpdating = False
Sheets("Info_Sheet").Select
Sheets("ProWatch Data").Visible = True
Sheets("ProWatch Data").Select
Range("A2").Select
currentrow = currentrow - 1
If currentrow > 1 Then
txtReader00.Text = Cells(currentrow, 1).Value
txtReader01.Text = Cells(currentrow, 2).Value
txtInput1.Text = Cells(currentrow, 3).Value
txtInput2.Text = Cells(currentrow, 4).Value
txtInput3.Text = Cells(currentrow, 5).Value
txtInput4.Text = Cells(currentrow, 6).Value
txtOutput1.Text = Cells(currentrow, 7).Value
txtOutput2.Text = Cells(currentrow, 8).Value
txtOutput3.Text = Cells(currentrow, 9).Value
txtOutput4.Text = Cells(currentrow, 10).Value
Application.ScreenUpdating = True
Sheets("Info_Sheet").Select
ElseIf currentrow = 1 Then
MsgBox "This is your first Record"
currentrow = currentrow + 1
Sheets("Info_Sheet").Select
Range("A2").Select
Application.ScreenUpdating = True
End If


End Sub


Private Sub cmdSend_Click()
Application.ScreenUpdating = False
Sheets("Prowatch Data").Visible = True
Sheets("ProWatch Data").Select
Range("A2").Select
'ActiveCell.End(xlDown).Select
'lastrow = ActiveCell.Row
' MsgBox lastrow
Cells(currentrow, 1).Value = txtReader00.Text
Cells(currentrow, 2).Value = txtReader01.Text
Cells(currentrow, 3).Value = txtInput1.Text
Cells(currentrow, 4).Value = txtInput2.Text
Cells(currentrow, 5).Value = txtInput3.Text
Cells(currentrow, 6).Value = txtInput4.Text
Cells(currentrow, 7).Value = txtOutput1.Text
Cells(currentrow, 8).Value = txtOutput2.Text
Cells(currentrow, 9).Value = txtOutput3.Text
Cells(currentrow, 10).Value = txtOutput4.Text
Range("A2").Select
txtReader00.Text = ""
txtReader01.Text = ""
txtInput1.Text = ""
txtInput2.Text = ""
txtInput3.Text = ""
txtInput4.Text = ""
txtOutput1.Text = ""
txtOutput2.Text = ""
txtOutput3.Text = ""
txtOutput4.Text = ""
Sheets("ProWatch Data").Visible = False
Sheets("Info_Sheet").Select
Range("A2").Select
Application.ScreenUpdating = True
End Sub




Private Sub UserForm_initialize()
Sheets("ProWatch Data").Visible = True
Sheets("ProWatch Data").Select
Range("A2").Select
Sheets("Info_Sheet").Select
Range("A2").Select
currentrow = 1
If currentrow = 1 Then
MsgBox "You are now in the header row. Click Next to see the first data"
txtReader00.Text = ""
txtReader01.Text = ""
txtInput1.Text = ""
txtInput2.Text = ""
txtInput3.Text = ""
txtInput4.Text = ""
txtOutput1.Text = ""
txtOutput2.Text = ""
txtOutput3.Text = ""
txtOutput4.Text = ""
Sheets("Info_Sheet").Select
Range("A2").Select
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Sequential Numbering of New Records using VBA
2
Replies
10
Views
983
dmt32
D
A
  • Question
How to insert paragraphs between text and then export to word
Replies
1
Views
435
Micron
M
T
  • Question
[VBA] Compare Two Sheets, Highlight Differences, But Consider Add/Remove Rows
Replies
0
Views
959
TimidBee
T
M
  • Question
One Userform button to fill in TWO separate tables
2
Replies
14
Views
1K
dmt32
D
G
  • Solved
Send data from a combobox on a userform to table in spreadsheet
Replies
3
Views
348
Gem866
G

Forum statistics

Threads
1,203,694
Messages
6,056,762
Members
444,890
Latest member
war24

Share this page

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