Solved: Excel 2003 Userform not posting until its closed

[simora]

I've previously posted this at
http://www.mrexcel.com/forum/showthread.php?p=2222430&posted=1#post2222430

but no solutions. I'll inform them also.

I have a Userform which is set up to post data to two seperate workbooks. I cannot get it to post the data until I close the Userform. Then, the data is posted correctly. ( But only after the form is closed )Any suggestions on how to have this working correctly, so that I can enter new data without first closing the Userform?.

I've tried setting all the controls to Ctrl.Value=vbnullstring , setting all the textboxes etc.. to " " & me. refresh etc..etc Still I'm stumped.

I’ve found out that this part of the code which assigns the next number in a series to the account number is probably what’s causing problems.

 
LlastRw = wsSheet.Range("A65536").End(xlUp).Row
        NexRw = wsSheet.Range("A65536").End(xlUp).Offset(1, 0).Row
    
        Set StAdd = Range("A65536").End(xlUp)
        Set EnAdd = Range("A65536").End(xlUp).Offset(1, 0)
        Range("A65536").End(xlUp).Select
If ActiveCell.Row = 2 Then
              ActiveCell.Offset(1, 0).Select
              MyString = (ActiveSheet.Index & "0001")
              firstpart = ("0" & ActiveSheet.Index & "-")
                '   ActiveCell.Value = firstpart & MyString
              
              fullpart = (firstpart & MyString)
              Label2.Caption = (firstpart & MyString)
              ClientAccount = ActiveCell.Value
           
        Else
       
            Selection.AutoFill Destination:=Range(StAdd, EnAdd), Type:=xlFillDefault
            
            Range("A65536").End(xlUp).Select
            ClientAccount = ActiveCell.Value
            
            Me.Label2.Caption = ClientAccount
    
End If

It correctly assigns the number, but it somehow causes the form to NOT post the results.

I tried sending it to another Userform, and it shows up correctly, but it still does NOT post it to the worksheet.

Need your help.

Thanks

[mikerickson]

In that code no data is passing from the Userform to the sheet.
This is the only line that changes what is on the worksheet.[VBA]Selection.AutoFill Destination:=Range(StAdd, EnAdd), Type:=xlFillDefault[/VBA]And that line runs only if the last cell in Column A is below the second row, in which case the last cell's value will be filled into the cell below it.

What is the code that takes values from the userform and puts it into worksheets? Also, what module holds that code?

[simora]

mikerickson :

I'm attaching 2 sample sheets that demonstrate the problem. You'll notice that if you enter basic info

Account#
First Name
Last Name
Company Name

the information does not get posted to the worksheets, however, if you close the Userform, then, it gets posted. I have omitted the code which assigns the account number automatically.

[lucas]

Looks to me like this is the problem from a brief look at it:

[VBA]Workbooks("Book2.xls").Activate[/VBA]

There is no need for it and if you comment it out it seems to do what you want.

I suggest strongly that you put option explicit at the top of each of your modules as you have quite a few variables not defined.

[lucas]

Simora, there are more errors which you are hiding with this line:

[VBA] On Error Resume Next[/VBA]

[simora]

Lucas:

Its not that the data is not there, and commenting out
Workbooks("Book2.xls").Activate

didn't affect the outcome. I've tried this with every variable declared correctly. Same result. There's got to be some simple reason where the data is being held until the form is closed. I just dont know where.

Workbooks("Book2.xls").Activate is a holdover from some other code that was needed to activate that workbook in order to execute.

Thanks for looking at it.

[lucas]

Ok, This works but you have a problem clearing your controls. We will look at that in a minute. Check this out and see if it doesn't do what you want.

I select sheet 3 from the salesperson name dropdown and and enter text in the first name, last name, company name, and account number drop downs with both workbooks open and it navigates to sheet 3 and enters the data there with the userform open.

[VBA]Option Explicit
Private Sub cmdEnterData_Click()
' TO POST NEW CLIENT DATA


Dim sText As String

Dim LlastRw As Long
Dim NexRw As Long
Dim StAdd As Range
Dim EnAdd As Range
Dim NewACnum
Dim Numbr As Long
Dim SrNumbr
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim MyString As String
Dim firstpart As String
Dim ClientAccount As String
Dim Ctrl As Control
Dim fullpart
Dim dNum
Dim strTime
Dim strDate
Dim c As Range
Dim n



Dim pos ' SHOW BALANCE
strTime = Time
strDate = Date
strDate = Format(Date, "dddd/mmmm/dd/yyyy")
If ComboBox2.Value = "" Then
MsgBox " Do not select the Masterlist to Add a New Client "
ComboBox2 = ""
ComboBox2.SetFocus
Exit Sub
End If
' next empty cell in column A
Set wbBook = ThisWorkbook
Set wsSheet = Worksheets(ComboBox2.Value)

wsSheet.Activate
Set c = wsSheet.Range("B65536").End(xlUp).Offset(1, 0)

' Application.ScreenUpdating = False 'speed up, hide task
' Range("B65536").End(xlUp).Offset(2, 0).Select
' write userform entries to Weekly Sales database
' On Error Resume Next
' write Client Account Number

c.Value = Me.TextBox2.Value ' This is Column B Client's Company Name
c.Offset(0, -1).Value = Me.TextBox16.Value ' This is Column I Start Edition

' Workbooks("Book2.xls").Activate

' next empty cell in column A
Set n = Workbooks("Book2.xls").Sheets("NetworkSheet").Range("A65536").End(xlUp).Off set(1, 0)
' Sheets("NetworkSheet").Range("A65536").End(xlUp).Offset(1, 0).Select

n.Value = TextBox16.Value ' This is Column A a Client Account Number

' n.Offset(0, 1).Value = ComboBox1.Value ' Salutation
n.Offset(0, 2).Value = tbxFirst.Value ' This is Column C Client's First Name
n.Offset(0, 3).Value = tbxLast.Value
'n.Offset(0, 4).Value = TextBox1.Value ' This is Column E Business-Title
n.Offset(0, 5).Value = TextBox2.Value ' This is the Company-Name
'For Each Ctrl In Me.Controls
' Ctrl.Value = vbNullString
'Next Ctrl
End Sub[/VBA]

[lucas]

And, if you uncomment this line:

[VBA] Workbooks("Book2.xls").Activate[/VBA]

from the code in post 7, it will activate book2 and enter the data with the userform still open.

Not sure which is your objective.

[lucas]

This is what I finished up with to clear the controls and make sure data goes on qualified workbooks and sheets. Hope I understood your intent:

[VBA]Option Explicit
Private Sub cmdEnterData_Click()
' TO POST NEW CLIENT DATA


Dim sText As String

Dim LlastRw As Long
Dim NexRw As Long
Dim StAdd As Range
Dim EnAdd As Range
Dim NewACnum
Dim Numbr As Long
Dim SrNumbr
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim MyString As String
Dim firstpart As String
Dim ClientAccount As String
Dim Ctl As Control
Dim fullpart
Dim dNum
Dim strTime
Dim strDate
Dim c As Range
Dim n



Dim pos ' SHOW BALANCE
strTime = Time
strDate = Date
strDate = Format(Date, "dddd/mmmm/dd/yyyy")
If ComboBox2.Value = "" Then
MsgBox " Do not select the Masterlist to Add a New Client "
ComboBox2 = ""
ComboBox2.SetFocus
Exit Sub
End If
' next empty cell in column A
Set wbBook = ThisWorkbook
Set wsSheet = ThisWorkbook.Worksheets(ComboBox2.Value)

wsSheet.Activate
Set c = wsSheet.Range("B65536").End(xlUp).Offset(1, 0)

' Application.ScreenUpdating = False 'speed up, hide task
' Range("B65536").End(xlUp).Offset(2, 0).Select
' write userform entries to Weekly Sales database
' On Error Resume Next
' write Client Account Number

c.Value = Me.TextBox2.Value ' This is Column B Client's Company Name
c.Offset(0, -1).Value = Me.TextBox16.Value ' This is Column I Start Edition

Workbooks("Book2.xls").Activate

' next empty cell in column A
Set n = Workbooks("Book2.xls").Sheets("NetworkSheet").Range("A65536").End(xlUp).Off set(1, 0)
' Sheets("NetworkSheet").Range("A65536").End(xlUp).Offset(1, 0).Select

n.Value = TextBox16.Value ' This is Column A a Client Account Number

' n.Offset(0, 1).Value = ComboBox1.Value ' Salutation
n.Offset(0, 2).Value = tbxFirst.Value ' This is Column C Client's First Name
n.Offset(0, 3).Value = tbxLast.Value
'n.Offset(0, 4).Value = TextBox1.Value ' This is Column E Business-Title
n.Offset(0, 5).Value = TextBox2.Value ' This is the Company-Name
'For Each Ctrl In Me.Controls
' Ctrl.Value = vbNullString
'Next Ctrl
For Each Ctl In Me.Controls
If TypeName(Ctl) = "TextBox" Then
Me.Controls(Ctl.Name).Text = ""
ElseIf TypeName(Ctl) = "ComboBox" Then
Me.Controls(Ctl.Name).ListIndex = -1
End If
Next
End Sub[/VBA]

[simora]

lucas:

You code WORKS ! Thanks

I will modify as needed.

The code I originally posted is supposed to increment a series. Is there an easy way to increment a number like 010-100008 to 010-100009 etc..etc but if its the first number in the series, use the ActiveSheet.Index to set the number series.

Again. Thanks

[lucas]

ah, that code wasn't in the files you posted. It's really getting late so I will take a look at it tommorrow.

Maybe Mike or someone will come along in the meantime and help.

It seems Mike was actually addressing your first concern while I only looked at why it wasn't posting data to the sheet until you closed the userform.

[simora]

Lucas:
RE: I only looked at why it wasn't posting data to the sheet until you closed the userform.

Since that was the main issue, I took all that other stuff out.

Have a good night

Thanks

[mikerickson]

Try this for incrementing serial numbers.

[VBA]Dim wsSheet As Worksheet
Set wsSheet = ThisWorkbook.Sheets("Sheet1")

Dim Size As Long, numFormat As String
Size = 6
numFormat = "000""-""" & String(Size, "0")

With wsSheet.Range("A:A")
With .Cells(.Rows.Count, 1).End(xlUp)
If .Row = 1 Then
.Offset(1, 0).Value = Format((.Parent.Index * (10 ^ Size)), numFormat)
Else
.Offset(1, 0).Value = Format(Val(Application.Substitute(CStr(.Value), "-", vbNullString)) + 1, numFormat)
End If
End With
End With
[/VBA]

[simora]

mikerickson :

Thanks.

I tried to post this earlier. Not sure why it didn't show up.

I got some ideas that worked at this link.
To increment the string by one within the same cell

http://www.ozgrid.com/forum/showthread.php?t=18852

Is there a problem with posting links to this forum ?

[lucas]

Did you use the hyperlink button on the posting toolbar?

[simora]

Got It.

Thanks

[samee]

pl z tell me vbs code how i auto submit thre web form ,webform useig the image button

here is code **********************************************

<html>
<head>
<title>Send me my password</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="style.css" type="text/css">
</head>
<body style="vertical-align:middle;">
<div align="center" style="vertical-align:middle; ">
<table width="100%" border="0" cellspacing="0" cellpadding="10">
<tr>
<td style="vertical-align:middle; height:100%; text-align:center;">

<h1>Send me my password</h1>
<form action="" method="post" >
<p>Enter your phone number<br><br><input name="MSISDN" type="text" id="MSISDN"><br><br>
</p>
<p><input name="imageField" type="image" src="ok.gif" border="0"></p>
</form>
</td>
</tr>
</table>
</div>
</body>
*****************************************
kindly email me at samee73@gmail.com

[Tinbendr]

I cannot get it to post the data until I close the Userform. Then, the data is posted correctly. ( But only after the form is closed )

That is normal for userforms. The screen is not updated while the userform has focus.

To update the screen at the end of the click event, add
[VBA]Application.ScreenRefresh[/VBA]