Submit Macro

G

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
767
Office Version
  1. 365
Platform
  1. Windows
I am using VBA to save a form ( in Microsoft Excel 2007 ), but i need it to save to a different sheet in a workbook depending on the managers name
cmbMgr is the reference i need to match the data to.
I have 6 managers that it can be ( 6 seperate sheets ) that the below info needs to go into when saved

The macro below saves all the data to 1 sheet at the moment but needs splitting as above

Private Sub CommandButton1_Click()

If Me.txtdate.Value = "" Then
MsgBox "Please complete todays date"
Else
If Me.cmbMgr.Value = "" Then
MsgBox "You need to choose your TM name before you can raise a Car Park Query"
Else
If Me.cmbAdvocate.Value = "" Then
MsgBox "You need to complete the customer profile number before you can raise a Car Park Query"
Else
If Me.TextBox5.Value = "" Then
MsgBox "You need to complete the account number"
Else
If Me.TextBox3.Value = "" Then
MsgBox "You need to complete the customer name"
Else
If Me.ComboBox3.Value = "" Then
MsgBox "You need to complete your question catogery"
Else
If Me.TextBox6.Value = "" Then
MsgBox "You need to complete your question"
Else

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\Data.xls"

Dim myBook As Workbook

On Error Resume Next
Set myBook = Application.Workbooks("Data.xls")
On Error GoTo 0

If myBook.ReadOnly Then
MsgBox "This file is being used by someone else please try again in a minute"
ActiveWorkbook.Close False
Else

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ActiveWorkbook.Sheets("Sheet1").Activate
ActiveSheet.AutoFilterMode = False

Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell = txtdate
ActiveCell.Offset(0, 1) = txtdate.Value
ActiveCell.Offset(0, 2) = cmbMgr.Value
ActiveCell.Offset(0, 3) = cmbAdvocate.Value
ActiveCell.Offset(0, 4) = ComboBox1.Value
ActiveCell.Offset(0, 5) = TextBox5.Value
ActiveCell.Offset(0, 6) = TextBox3.Value
ActiveCell.Offset(0, 7) = TextBox4.Value
ActiveCell.Offset(0, 8) = ComboBox2.Value
ActiveCell.Offset(0, 9) = ComboBox3.Value
ActiveCell.Offset(0, 10) = TextBox6.Value





'clear the data
Me.txtdate.Value = ""
Me.cmbMgr.Value = ""
Me.cmbAdvocate.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox5.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.TextBox6.Value = ""

Windows("Car Park v2.xls").Activate
ActiveWindow.Close (True)
Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

MsgBox "Your Car Park has been submitted"
Unload Me
End If


End If
End If
End If
End If
End If
End If
End If
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Try changing this
Code: 
    Set ws = Worksheets("Sheet1")
    ActiveWorkbook.Sheets("Sheet1").Activate
    ActiveSheet.AutoFilterMode = False
    Range("A1").Select
    Do
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell = txtdate
    ActiveCell.Offset(0, 1) = txtdate.Value
    ActiveCell.Offset(0, 2) = cmbMgr.Value
    ActiveCell.Offset(0, 3) = cmbAdvocate.Value
    ActiveCell.Offset(0, 4) = ComboBox1.Value
    ActiveCell.Offset(0, 5) = TextBox5.Value
    ActiveCell.Offset(0, 6) = TextBox3.Value
    ActiveCell.Offset(0, 7) = TextBox4.Value
    ActiveCell.Offset(0, 8) = ComboBox2.Value
    ActiveCell.Offset(0, 9) = ComboBox3.Value
    ActiveCell.Offset(0, 10) = TextBox6.Value

to this:
Code: 
    Set ws = Worksheets(cmbMgr.Value)
    ws.AutoFilterMode = False
    ws.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 10).Value = _
    Array(txtdate.Value, cmbMgr.Value, cmbAdvocate.Value, ComboBox1.Value, TextBox5.Value, _
          TextBox3.Value, TextBox4.Value, ComboBox2.Value, ComboBox3.Value, TextBox6.Value)
 
Upvote 0
Sorry,
I am a novice with VBA.
Would this reference the info in cmbmgr ( managers name ) and then place it on the sheet in the destination file with the same managers name?
Cheers
 
Upvote 0
It uses the sheet with the name in cmbMgr instead of Sheet1.
 
Upvote 0
Good Afternoon,

Sorry it has taken me so long but i have used the VBA as above but i am getting an error as follows

Compile error:

Invalid or unqalified reference!

Any idea how i sort this

Thanks
 
Upvote 0
Maybe posting your code and explaining what line gives the error would be a good place to start.
 
Upvote 0
Sorry,

This is the VBA i am using .Rows. below is giving the error above

Private Sub cmdsubmit_Click()
If Me.txtdate.Value = "" Then
MsgBox "Please complete todays date"
Else
If Me.cmbMgr.Value = "" Then
MsgBox "You need to choose your TM name before you can raise a Car Park Query"
Else
If Me.cmbAdvocate.Value = "" Then
MsgBox "You need to complete the customer profile number before you can raise a Car Park Query"
Else
If Me.TextBox5.Value = "" Then
MsgBox "You need to complete the account number"
Else
If Me.TextBox3.Value = "" Then
MsgBox "You need to complete the customer name"
Else
If Me.ComboBox3.Value = "" Then
MsgBox "You need to complete your question catogery"
Else
If Me.TextBox6.Value = "" Then
MsgBox "You need to complete your question"
Else

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\Data.xls"

Dim myBook As Workbook

On Error Resume Next
Set myBook = Application.Workbooks("Data.xls")
On Error GoTo 0

If myBook.ReadOnly Then
MsgBox "This file is being used by someone else please try again in a minute"
ActiveWorkbook.Close False
Else

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(cmbMgr.Value)
ws.AutoFilterMode = False
ws.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 10).Value = _
Array(txtdate.Value, cmbMgr.Value, cmbAdvocate.Value, ComboBox1.Value, TextBox5.Value, _
TextBox3.Value, TextBox4.Value, ComboBox2.Value, ComboBox3.Value, TextBox6.Value)

'clear the data
Me.txtdate.Value = ""
Me.cmbMgr.Value = ""
Me.cmbAdvocate.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox5.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.TextBox6.Value = ""

Windows("Data.xls").Activate
ActiveWindow.Close (True)
Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

MsgBox "Your Car Park has been submitted"
Unload Me
End If


End If
End If
End If
End If
End If
End If
End If
End Sub
 
Upvote 0
Code: 
ws.Cells([COLOR=red][B]ws[/B].Rows[/COLOR].Count, "A").End(xlUp).Offset(1).Resize(, 10).Value = ...
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Move existing row of value to another selected row
Replies
1
Views
131
ipbr21054
I
E
  • Solved
ActiveCell vs Active range search
Replies
3
Views
222
Joe4
Joe4
C
  • Question
Adding a hyperlink to an automated email
Replies
1
Views
467
Micron
M
I
  • Question
Copy worksheet but the new worksheet to omit some items
2
Replies
11
Views
428
ipbr21054
I
I
  • Question
Advice for if cell has a value then
Replies
7
Views
268
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,214,805
Messages
6,121,665
Members
449,045
Latest member
Marcus05

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