Home VBA Excel : Subroutine work but if called, It no longer works
Reply: 2

VBA Excel : Subroutine work but if called, It no longer works

Flaniganga
1#
Flaniganga Published in 2017-12-07 11:43:30Z

Hi there Stuck with a CALL problem

I created a subroutine called Daily_Proto_1 It copies data from the user form to my worksheet : This is the code:

Public Sub Daily_Proto_1()
Dim LastRow As Long, ws As Worksheet

    Set ws = Sheets("DailyRep")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

'Adds the TextBox1 into Col A & Last Blank Row
'Adds the TextBox2 into Col B & Last Blank Row
'Adds the ComboBox1 into Col C & Last Blank Row
'Ect.

    ws.Range("A" & LastRow).Value = TextBox1.Text
    ws.Range("B" & LastRow).Value = TextBox2.Text
    ws.Range("C" & LastRow).Value = ComboBox1.Text
    ws.Range("D" & LastRow).Value = TextBox3.Text
    ws.Range("E" & LastRow).Value = ComboBox2.Text
    ws.Range("F" & LastRow).Value = ComboBox3.Text

End Sub

Now in my User form I have a submit button that should call this routine as stipulated below:

Private Sub CommandButton1_Click()

Call Module1.Daily_Proto_1

End Sub

The reason for this is so that I can include this in another button, but will have some additional instructions. When I run the code under the Private Sub CommandButton1_Click() it runs perfectly, however when i move it to the Public Procedure, it gives me the following error:

When I click on debug it takes me to the first line

Can anyone tell me what I am doing wrong here please

MarcinSzaleniec
2#
MarcinSzaleniec Reply to 2017-12-07 11:49:46Z

Add a name of your userform prior to TextBox1. For instance

 ws.Range("A" & LastRow).Value = UserForm1.TextBox1.Text

Or rather use

with UserForm1

end with 

and dots beforre control names.

Oliver Humphreys
3#
Oliver Humphreys Reply to 2017-12-07 11:50:33Z

I think you may need to stipulate the name of your userform before textbox1.text.

ws.Range("A" & LastRow).Value = UserForm1.textbox1.text

(replace Userform1 with the name of your userform)

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.311617 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO