Sorry didnt check before i sent clip above.
This clip shows you what i type on the userform and what is shown in row 21
Sorry didnt check before i sent clip above.
This clip shows you what i type on the userform and what is shown in row 21
Watch this short clip where i add to row 21
Phone number works but post code isnt spaced
Watch here
I dont understand what you are saying.
I supplied the worksheet that i am using and is working.
The only thing on it that doesnt work is the post code part.
The code in that sheet works fine just needes some code added for post code then complete
Attached as requested.
Thanks
The code below works fine BUT like i mentioned ive lost the post ocde part where a space is added Thus
I type BS296HD but i need to then apply a space so on the worksheet its shown as BS29 6HD
Please can you edit my code below as apart from that its working.
The case number i use must be -1 otherwise i see 0 in a cell thats not required
Private Sub CommandButton1_Click()
Dim i As Integer
Dim ControlsArr As Variant, ctrl As Variant
Dim x As Long
Dim z As Integer
z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))
For i = 1 To 11
With Me.Controls("TextBox" & i)
If .Text = "" Then
MsgBox "ALL FIELDS MUST BE COMPLETED", 48, "GRASS NEW CUSTOMER FORM"
.SetFocus
Exit Sub
End If
End With
Next i
ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
With ThisWorkbook.Worksheets("GRASS")
.Rows(z).EntireRow.Insert Shift:=xlDown
For i = 0 To UBound(ControlsArr)
Select Case i
Case -1
.Cells(z, i + 1) = Val(ControlsArr(i))
ControlsArr(i).Text = ""
Case Else
.Cells(z, i + 1) = ControlsArr(i)
ControlsArr(i).Text = ""
End Select
Next i
End With
ActiveWorkbook.Save
MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
Unload GRASSNEWCUSTOMER
End Sub
Display More
This is what works for me apart from 2 things so im happy to use this.
Ive lost the code that spaces the post code,
I type BS296HD but on worksheet it stays the same and not BS29 6HD
Private Sub CommandButton1_Click()
Dim i As Integer
Dim ControlsArr As Variant, ctrl As Variant
Dim x As Long
Dim z As Integer
z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))
For i = 1 To 11
With Me.Controls("TextBox" & i)
If .Text = "" Then
MsgBox "ALL FIELDS MUST BE COMPLETED", 48, "GRASS NEW CUSTOMER FORM"
.SetFocus
Exit Sub
End If
End With
Next i
ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
With ThisWorkbook.Worksheets("GRASS")
.Rows(z).EntireRow.Insert Shift:=xlDown
For i = 0 To UBound(ControlsArr)
Select Case i
Case -1
.Cells(z, i + 1) = Val(ControlsArr(i))
ControlsArr(i).Text = ""
Case Else
.Cells(z, i + 1) = ControlsArr(i)
ControlsArr(i).Text = ""
End Select
Next i
End With
ActiveWorkbook.Save
MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
Unload GRASSNEWCUSTOMER
End Sub
Private Sub CommandButton2_Click()
Unload GRASSNEWCUSTOMER
Range("A4").Select
End Sub
Private Sub TextBox6_AfterUpdate()
TextBox6.Value = Format(TextBox6.Value, "£#,##0.00")
End Sub
Private Sub TextBox10_AfterUpdate()
TextBox10.Value = Format(TextBox10.Value, "£#,##0.00")
End Sub
Private Sub TextBox1_Change()
TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
TextBox2 = UCase(TextBox2)
End Sub
Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case Len(TextBox3)
Case 5, 6
With TextBox3
.Text = .Text & "-"
.SelStart = Len(.Text)
End With
End Select
End Sub
Private Sub TextBox4_Change()
TextBox4 = UCase(TextBox4)
End Sub
Private Sub TextBox5_Change()
TextBox5 = UCase(TextBox5)
End Sub
Private Sub TextBox9_Change()
TextBox9 = UCase(TextBox9)
End Sub
Private Sub UserForm_Initialize()
'-------------------------------
Me.StartUpPosition = 0
Me.Top = Application.Top + 15 ' MARGIN FROM TOP OF SCREEN
Me.Left = Application.Left + Application.Width - Me.Width - 160 ' LEFT / RIGHT OF SCREEN
'-------------------------------
Display More
That also does not add a space between phone number
Can we not & would it be easier / quicker to have a change event code like,but obviously written correctly so,,,,
Range = C
for any cell in range
if number is 00000000000
then change it to 00000 000000
using the case format code like case 5 6 etc
Obviously above is miles out hence why im in this group
That didnt apply the space
Sorry royUK just missed you.
Its for UK phone number TextBox3 then inserted into worksheet in column C
I might of put it in the wrong code so now ive tried on the actual TextBo3 in the userform like so but second line of advised code changes yellow.
So i have this shown below & when transfered to worksheet it will fit into column C
I assume i then add it into the comman button code /
If so not sure where to insert it as it keeps changing to red.
Private Sub CommandButton1_Click()
Dim i As Integer
Dim ControlsArr As Variant, ctrl As Variant
Dim x As Long
Dim z As Integer
z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))
For i = 1 To 11
With Me.Controls("TextBox" & i)
If .Text = "" Then
MsgBox "ALL FIELDS MUST BE COMPLETED", 48, "GRASS NEW CUSTOMER FORM"
.SetFocus
Exit Sub
End If
End With
Next i
ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
With ThisWorkbook.Worksheets("GRASS")
.Rows(z).EntireRow.Insert Shift:=xlDown
For i = 0 To UBound(ControlsArr)
Select Case i
Case 3
Dim str As String
str = Trim(ControlsArr(i))
str = Left(str, 3) & " " & Right(str, 3)
.Cells(z, i + 1) = str
ControlsArr(i).Text = ""
Case Else
.Cells(z, i + 1) = ControlsArr(i)
ControlsArr(i).Text = ""
End Select
Next i
Cells(1,1).value=Me.TextBox 4.value
Cells(1, 1).Value.NumberFormat = "00000 000000"
End With
Application.ScreenUpdating = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
With ThisWorkbook.Worksheets("GRASS")
.Range("A5").Select
.Range("A4").Select
MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
End With
Unload GRASSNEWCUSTOMER
End Sub
Display More
Morning,
Gijsmo you were correct as it was a custom format so many thanks as i spent an hour again yesterday going through each module only not to find it.
Like mentioned in post #1 i needed to find it as i wanted to use it again on another worksheet.
So now ive found it i dont think i can use it because i insert a new row into a worksheet at various positions hence not knowing at the time which position it will be.
So i need to make this code using vba only & place it in the selection change event.
Please can you advise a vba code so when a number is inserted in column C the code will space it like 00000000000 to 00000 000000
Have a nice day
FYI i removed all the code for the worksheet,right click tab & cut / save and still the code runs.
So narrowing it down but where is it ?
On my worksheet at column W i would enter a phone number & the code i am unable to locate "as i need to use elsewhere" would space it for me.
Example i would enter 07899843147 & when i leave the cell in question i would see it change to 07899 843147
It would enter a space.
Now from memory the code had some kind of symbol like @@@@@/@@@@@@
Ive looked on the worksheet & also in the modules but cant find this code.
Do you have any advice of how i can track it down.
Thanks
Many thanks again now fine.
Have a nice day.
Ok i understand what youve mentioned.
what i dont understand is why ive got it there in the first place ??
What do you advise this code to be like as i just see it as pointless for it to be there.
Hi,
I have a code which works fine in respect of sending values from my userform to my worksheet.
However there is one issue.
TextBox4 on my userform is expecting a post code to be entered.
Once i transfer values to worksheet this post code should be placed into the cell at column D
What actually is pasted into this cell is 0 "zero"
Here is the code that relates to it.
Changing Case 3 to -1 does the trick BUT what is it there for to start with ?
Thanks
Many thanks for your help / time with this.