UserForm

S

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Hi everyone I have a sub that works in a module but not a userform, would anyone help me to modify this to work in my userform. I need to learn from any changes you make so that I can do it to a couple others. I never new you could write scripts in the same way.

Right now im getting hung up on the part of "Set Found" in red...

Code: 
Private Sub CommandButton1_Click() 'input
Dim ws As Worksheet
Dim wb As Workbook
Dim FindValue As String, Box As String, putName As String, FindName As String, FindNumber As String, Found As String
Dim RowNum As Integer, ColNum As Integer
Dim r As Integer
Application.ScreenUpdating = False
FindValue = Me.Calendar1.Value
If Me.OptionButton1 = True Then
Box = "O"
End If
If Me.OptionButton2 = True Then
Box = "P"
End If
    On Error GoTo finish
    Range("P11:AD75").Select
   Selection.Find(What:=FindValue, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    On Error GoTo 0
    RowNum = ActiveCell.Row
    ColNum = ActiveCell.Column
If Me.CheckBox1 = True Then
    putName = Me.TextBox1.Value & " " & Me.TextBox2.Value
    For r = 1 To 10
        If Cells(RowNum + r, ColNum) = "" Or Cells(RowNum + r, ColNum) = putName Then
            Cells(RowNum + r, ColNum) = putName
            Cells(RowNum + r, ColNum + 1) = Box
            
    'begin part that inputs the letter to Schedule Template
FindName = Me.TextBox1.Value & " " & Me.TextBox2.Value
FindNumber = Me.Calendar1.Value
For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
ws.Visible = True
ws.Rows.Hidden = False
ws.Select
ActiveSheet.Range("A:A").Select
     On Error GoTo finish
        [COLOR=red]Set Found[/COLOR] = ws.Columns("A:A").Find(What:=FindNumber, After:=Range("A3"), LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If Not[COLOR=red] Found[/COLOR] Is Nothing Then
   
    Selection.FindNext(After:=ActiveCell).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Find(What:=FindName, After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Select
        
    RowNum = ActiveCell.Row
    ColNum = ActiveCell.Column
    Cells(RowNum, ColNum + 88) = Box
    End If
 
Next
'end part that inputs the letter to Schedule Template
            For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
            ws.Visible = False
            Next
            Sheets("MyStoreInfo").Select
            Range("Q9").Select
            Application.ScreenUpdating = True
            Exit Sub
            
            ElseIf Cells(RowNum + r, ColNum + 1) = putName Then
            
            MsgBox ("You have already input that name here.")
            Range("Q9").Select
            Application.ScreenUpdating = True
            Exit Sub
    
            Else
            If r = 10 Then
                MsgBox ("No more room")
                Range("Q9").Select
                Application.ScreenUpdating = True
                Exit Sub
            End If
            
        End If
    Next
            
finish:
    Err.Clear
    For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
    ws.Visible = False
    Next
    Range("Q9").Select
    Application.ScreenUpdating = True
    MsgBox ("Date not found")
End If
End Sub

thanks for any help or direction.

sd
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
It's probably hanging up because you're telling it to look after Range("A3") in a userform.

You may have to use ActiveSheet.Range("A3") instead
 
Upvote 0
It should be ws.Range("A3")
 
Upvote 0
rorya said:
It should be ws.Range("A3")
Click to expand...


Thanks both for the help. Seems in still getting an error of "object required for "Found". Any chance you see what I am omitting?

here is what I have now:

Code: 
Private Sub CommandButton1_Click() 'input
Dim ws As Worksheet
Dim wb As Workbook
Dim FindValue As String, Box As String, putName As String, FindName As String, FindNumber As String, Found As String
Dim RowNum As Integer, ColNum As Integer
Dim r As Integer
Application.ScreenUpdating = False
FindValue = Day(Me.Calendar1.Value)
If Me.OptionButton1 = True Then
Box = "O"
End If
If Me.OptionButton2 = True Then
Box = "P"
End If
    On Error GoTo finish
    Range("P11:AD75").Select
   Selection.Find(What:=FindValue, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    On Error GoTo 0
    RowNum = ActiveCell.Row
    ColNum = ActiveCell.Column
If Me.CheckBox1 = True Then
    putName = Me.TextBox1.Value & " " & Me.TextBox2.Value
    For r = 1 To 10
        If Cells(RowNum + r, ColNum) = "" Or Cells(RowNum + r, ColNum) = putName Then
            Cells(RowNum + r, ColNum) = putName
            Cells(RowNum + r, ColNum + 1) = Box
            
    'begin part that inputs the letter to Schedule Template
FindName = Me.TextBox1.Value & " " & Me.TextBox2.Value
FindNumber = Me.Calendar1.Value
For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
ws.Visible = True
ws.Rows.Hidden = False
ws.Select
ws.Range("A:A").Select
     On Error GoTo finish
        Set Found = ws.Columns("A:A").Find(What:=FindNumber, After:=ws.Range("A3"), LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If Not Found Is Nothing Then
   
    Selection.FindNext(After:=ActiveCell).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Find(What:=FindName, After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Select
        
    RowNum = ActiveCell.Row
    ColNum = ActiveCell.Column
    Cells(RowNum, ColNum + 88) = Box
    End If
 
Next
'end part that inputs the letter to Schedule Template
            For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
            ws.Visible = False
            Next
            Sheets("MyStoreInfo").Select
            Range("Q9").Select
            Application.ScreenUpdating = True
            Exit Sub
            
            ElseIf Cells(RowNum + r, ColNum + 1) = putName Then
            
            MsgBox ("You have already input that name here.")
            Range("Q9").Select
            Application.ScreenUpdating = True
            Exit Sub
    
            Else
            If r = 10 Then
                MsgBox ("No more room")
                Range("Q9").Select
                Application.ScreenUpdating = True
                Exit Sub
            End If
            
        End If
    Next
            
finish:
    Err.Clear
    For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
    ws.Visible = False
    Next
    Range("Q9").Select
    Application.ScreenUpdating = True
    MsgBox ("Date not found")
End If
End Sub
Private Sub CommandButton2_Click() 'remove input
Response = MsgBox("This will delete all your calendar inputs, if you are sure you want to do so, press YES, if not Press NO.", vbQuestion + vbYesNo)
    If Response = vbYes Then
Application.ScreenUpdating = False
Dim OleObj As OLEObject
     
    For Each OleObj In ActiveSheet.OLEObjects
        If OleObj.progID = "Forms.CheckBox.1" Then
            OleObj.Object = False
        End If
    Next OleObj
    For Each OleObj In ActiveSheet.OLEObjects
        If OleObj.progID = "Forms.ComboBox.1" Then
            OleObj.Object = 15
        End If
    Next OleObj
    
    For Each OleObj In Sheets("Schedule Tool1").OLEObjects
        If OleObj.progID = "Forms.CheckBox.1" Then
            OleObj.Object = False
        End If
    Next OleObj
    For Each OleObj In Sheets("Schedule Tool1").OLEObjects
        If OleObj.progID = "Forms.ComboBox.1" Then
            OleObj.Object = 15
        End If
    Next OleObj
    
    Range("Q12:AD21,Q23:AD31,Q33:AD42,Q44:AD53,Q55:AD64,Q66:AD75").Select
    Selection.ClearContents
    For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
    ws.Visible = True
    ws.Rows.Hidden = False
    Next
    Sheets("Schedule Tool1").Select
    Columns("CK:CK").Select
    Selection.ClearContents
    Sheets("Schedule Tool2").Select
    Columns("CK:CK").Select
    Selection.ClearContents
    Sheets("Schedule Tool3").Select
    Columns("CK:CK").Select
    Selection.ClearContents
    Sheets("Schedule Tool4").Select
    Columns("CK:CK").Select
    Selection.ClearContents
    Sheets("Schedule Tool5").Select
    Columns("CK:CK").Select
    Selection.ClearContents
    For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
    ws.Visible = False
    Next
    Sheets("MyStoreInfo").Select
    Range("Q9").Select
Application.ScreenUpdating = True
    End If
End Sub

Thanks

sd
 
Upvote 0
Try ws.range("a:a") rather than ws.columns
 
Upvote 0
rorya said:
Try ws.range("a:a") rather than ws.columns
Click to expand...


that didnt work, does it matter that when the error is triggered, that the word "Found" and the = after found is also highlighted? would the = sign be cause an issue?

sd
 
Upvote 0
Try removing all the On Error statements in the code.
 
Upvote 0
Yes - you must declare Found as Range
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Myrna Larson-Permutations and Combinations
Replies
0
Views
295
Norman A
N
A
  • Question Question
VBA Combine macros in one depending on text string
Replies
2
Views
508
mumps
mumps
R
  • Question Question
Macro not working after adding two new items
Replies
5
Views
426
regan_2000
R
V
  • Question Question
Autofilter issue
Replies
0
Views
195
Vbanoob98
V
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
825
Gajendran Yadhav
G

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

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