Save button

R

Rvittur

New Member
Joined
Mar 8, 2023
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Following my last issue, which is solved. How can i get the save button on my userform to repeat the actions. Basically once all my selections are made, I click save to transfer the data to where it needs to go. however if i was to just start adding new selections i start getting errors. To avoid this i have to click exit and reclick the userform button.

this is the code i use on the save button

Private Sub cmdSave_Click()

Application.ScreenUpdating = False

Dim sch As Variant
Dim lRow As Long
Dim wsp As Worksheet
Dim wsp1 As Worksheet
Set wsp = Worksheets("Pickup")



lRow = wsp.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row

If cmdDate.Value >= "3/20" And cmdDate.Value <= "3/26" Then 'must be text
cmdSave_Click2
Call cmdSave_Click2

ElseIf cmdDate.Value >= "3/27" And cmdDate.Value <= "4/2" Then 'must be text
cmdSave_Click3
Call cmdSave_Click3

End If

''''Clear input''''
UserForm1.CmdEmployeeName.Value = ""
UserForm1.cmdDate.Value = ""
UserForm1.cmdTimeStart.Value = ""
UserForm1.cmdSuitDown.Value = ""
UserForm1.txtNotes.Value = ""
UserForm1.cmdDLRFLR.Value = ""
UserForm1.cmdTSC.Value = ""



MsgBox "Pickup Added", vbInformation



Application.ScreenUpdating = True


End Sub

Module1 hold the first round of codes needed for the 1st range of Dates.

Application.ScreenUpdating = False
Dim lRow As Long
Dim mRow As Long, mrow1 As Long, trow As Long, trow1 As Long, wrow As Long, wrow1 As Long, throw As Long, throw1 As Long, frow As Long, frow1 As Long, srow As Long, srow1 As Long, surow As Long, surow1 As Long
Dim wb1 As Workbook
Set wb1 = Workbooks("Pickup Form TestList")
Dim ws As Worksheet
Set ws = wb1.Worksheets("Pickup")

Dim wb As Workbook
Dim ws1 As Worksheet
Set wb = Workbooks.Open("C:\Users\jvittur\OneDrive\Desktop\Schedules\Sched Pickup 03.26.xlsm")
Set ws1 = wb.Worksheets("RoadMap")
Dim f As Range

lRow = ws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
mRow = ws1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
mrow1 = ws1.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
trow = ws1.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
trow1 = ws1.Cells(Rows.Count, 9).End(xlUp).Offset(1, 0).Row
wrow = ws1.Cells(Rows.Count, 13).End(xlUp).Offset(1, 0).Row
wrow1 = ws1.Cells(Rows.Count, 14).End(xlUp).Offset(1, 0).Row
throw = ws1.Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).Row
throw1 = ws1.Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).Row
frow = ws1.Cells(Rows.Count, 23).End(xlUp).Offset(1, 0).Row
frow1 = ws1.Cells(Rows.Count, 24).End(xlUp).Offset(1, 0).Row
srow = ws1.Cells(Rows.Count, 28).End(xlUp).Offset(1, 0).Row
srow1 = ws1.Cells(Rows.Count, 29).End(xlUp).Offset(1, 0).Row
surow = ws1.Cells(Rows.Count, 33).End(xlUp).Offset(1, 0).Row
surow1 = ws1.Cells(Rows.Count, 34).End(xlUp).Offset(1, 0).Row

With ws

.Cells(lRow, 2).Value = UserForm1.CmdEmployeeName.Value
.Cells(lRow, 3).Value = UserForm1.cmdDate.Value
.Cells(lRow, 1).Value = UserForm1.cmdTimeStart.Value
.Cells(lRow, 5).Value = UserForm1.cmdSuitDown.Value
.Cells(lRow, 7).Value = UserForm1.txtNotes.Value
.Cells(lRow, 4).Value = UserForm1.cmdDLRFLR.Value
.Cells(lRow, 6).Value = UserForm1.cmdTSC.Value
.Cells(lRow, 8).Value = UserForm1.TextBox1.Text

End With

If UserForm1.cmdSuitDown.Value <> "" And UserForm1.cmdTSC.Value <> "" And UserForm1.cmdDate = "3/20" Then
Set f = ws1.Range("D:D").Find(UserForm1.cmdSuitDown, , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
ws1.Range("C" & f.Row).Value = UserForm1.cmdTSC.Value
f.Interior.Color = vbYellow
End If
End If

If UserForm1.cmdDate.Value = "3/20" Then
wb.Worksheets("Roadmap").Activate
ws1.Cells(mRow, 3).Value = UserForm1.cmdTimeStart.Value
ws1.Cells(mrow1, 4).Value = UserForm1.CmdEmployeeName.Value

End If

With UserForm1
ws.Cells(lRow, 5).Value = UserForm1.cmdSuitDown.Value
ws.Cells(lRow, 3).Value = UserForm1.cmdDate.Value
End With
If UserForm1.cmdSuitDown.Value <> "" And UserForm1.cmdTSC.Value <> "" And UserForm1.cmdDate = "3/21" Then
Set f = ws1.Range("I:I").Find(UserForm1.cmdSuitDown, , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
ws1.Range("H" & f.Row).Value = UserForm1.cmdTSC.Value
f.Interior.Color = vbYellow
End If
End If

''''more code of repeat'''

''''Clear input''''
UserForm1.CmdEmployeeName.Value = ""
UserForm1.cmdDate.Value = ""
UserForm1.cmdTimeStart.Value = ""
UserForm1.cmdSuitDown.Value = ""
UserForm1.txtNotes.Value = ""
UserForm1.cmdDLRFLR.Value = ""
UserForm1.cmdTSC.Value = ""

Application.Windows("Sched Pickup "".xlsm").Visible = True

Application.ScreenUpdating = True

End Sub

Other code is on Date change since on combobox is based on its selection, where i get the errors at as well as on the UserForm Initialize
Private Sub UserForm_Initialize()

UserForm1.cmdSuitDown.List = Range("Monday").Value
UserForm1.CmdEmployeeName.List = Range("EmployeeName").Value
UserForm1.cmdDLRFLR.List = Range("DLRFLR").Value
UserForm1.cmdDate.List = Range("Date").Value
UserForm1.cmdTimeStart.List = Range("TimeStart").Value
UserForm1.cmdTSC.List = Range("TSC").Value

TextBox1.Value = Now
TextBox1 = Format(TextBox1.Value, "dd mmmm yyyy hh:mm")

End Sub


Private Sub cmdDate_Change()

With UserForm1.cmdDate.Value

If UserForm1.cmdDate.Value = "3/20" Then 'Monday
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex).Value
ElseIf UserForm1.cmdDate.Value = "3/21" Then 'Tues
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex + 4).Value

''''repeat of code with changes'''
ElseIf UserForm1.cmdDate.Value = "4/02" Then 'Sun
UserForm1.cmdSuitDown.Value = ""
UserForm1.cmdSuitDown.List = Range("Monday1").Offset(0, UserForm1.cmdDate.ListIndex + 17).Value

End If
End With
End If
End Sub

Would appreciate any advice or help

Thank You
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
If cmdDate.Value >= "3/20" And cmdDate.Value <= "3/26" Then 'must be text
cmdSave_Click2
Call cmdSave_Click2

Why are you calling "cmdSave_Click2" twice in a row.

What line of code fails and what is the error description

You have a sub routine listed without the sub name included
 
Upvote 0
Bill Rockenbach said:
If cmdDate.Value >= "3/20" And cmdDate.Value <= "3/26" Then 'must be text
cmdSave_Click2
Call cmdSave_Click2

Why are you calling "cmdSave_Click2" twice in a row.

What line of code fails and what is the error description

You have a sub routine listed without the sub name included
Click to expand...
sorry that would be Public Sub cmdSave_Click2()

method of object global fail

Private Sub cmdDate_Change()

With UserForm1.cmdDate.Value

If UserForm1.cmdDate.Value = "3/20" Then 'Monday
UserForm1.cmdSuitDown.List = Range("Monday").Offset(0, UserForm1.cmdDate.ListIndex).Value <-------Highlights
ElseIf UserForm1.cmdDate.Value = "3/21" Then 'Tues

Range("Monday") is linked to another workbook
 
Upvote 0
Rvittur, can you step through the code and get the exact line that fails. "method of object global fail" usually means a line of code is failing to access an object.
 
Upvote 0
See previous post where it has <----highlight is where it shows the error code
 
Upvote 0
Rvittur said:
I found the issue lmao..was with setting the workbook and worksheet lol
Click to expand...
Good to hear you found the issue.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
 
Upvote 0
Private Sub cmdSave_Click()

Application.ScreenUpdating = False
Dim wbp As Workbook
Set wbp = Workbooks("Pickup Form.xlsm")
Dim sch As Variant
Dim lRow As Long
Dim wsp As Worksheet
Dim wsp1 As Worksheet
Set wsp = wbp.Worksheets("Pickup") 'had to add the wbp.worksheets for it to work with the rest of code

lRow = wsp.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row '''finds last row of Pickup sheet

If cmdDate.Value >= "3/20" And cmdDate.Value <= "3/26" Then 'Date must be text. Due to named ranges code must be in module
Call cmdSave_Click2

ElseIf cmdDate.Value >= "3/27" And cmdDate.Value <= "4/2" Then 'Date must be text. Due to named ranges code must be in module
Call cmdSave_Click3

End If

''''Clear input''''
UserForm1.CmdEmployeeName.Value = ""
UserForm1.cmdDate.Value = ""
UserForm1.cmdTimeStart.Value = ""
UserForm1.cmdSuitDown.Value = ""
UserForm1.txtNotes.Value = ""
UserForm1.cmdDLRFLR.Value = ""
UserForm1.cmdTSC.Value = ""

MsgBox "Pickup Added", vbInformation

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
182
NdNoviceHlp
NdNoviceHlp
B
  • Solved
Populate UserForm ListBox array based on ComboBox Selection
Replies
9
Views
366
Ben888
B
P
  • Question Question
TextBox to count values from ComboBox item
Replies
1
Views
329
Patriot2879
P
B
  • Question Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
565
B-radK
B
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
624
myall_blues
myall_blues

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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