Title issue with user form

J

JarmoVee

New Member
Joined
May 27, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
What is wrong when UserForm1 shows titles (Column Heads) in the textboxes?
Cant find issue.


In the "Asiakashaku" sheet is named range and formula (SIIRTYMÄ=OFFSET in English)

Excel Formula: 
=SIIRTYMÄ(Asiakashaku!$A$2;0;0;LASKE.A(Asiakashaku!$A:$A)-1;18)

VBA Code: 
Private Sub cmdEtsi_asiakas_Click()
'Etsi toiminto lomakkeella

Dim RowNum As Long
Dim SearchRow As Long


RowNum = 2
SearchRow = 2

Worksheets("Asiakkaat").Activate

Do Until Cells(RowNum, 1).Value = ""

' Alla olevilla määritetään mistä sarakkeista haetaan (Eli sarakkeet 3,4,5)
    If InStr(1, Cells(RowNum, 3).Value, txtEtsi_asiakas.Value, vbTextCompare) > 0 _
    Or InStr(1, Cells(RowNum, 4).Value, txtEtsi_asiakas.Value, vbTextCompare) > 0 _
    Or InStr(1, Cells(RowNum, 5).Value, txtEtsi_asiakas.Value, vbTextCompare) > 0 Then
     
 
' Alla olevilla määritetään lomakkeen ikkunassa näkyvät sarakkeet (yht.18 saraketta)
    Worksheets("Asiakashaku").Cells(SearchRow, 1).Resize(, 18).Value = Cells(RowNum, 1).Resize(, 18).Value
    SearchRow = SearchRow + 1
   
    End If
    RowNum = RowNum + 1
Loop

If SearchRow = 2 Then
    MsgBox "Asiakasta ei löydy. Lisää uusi ja tallenna asiakaskortti!"
    Exit Sub
End If

lstHakuTulokset.RowSource = "Asiakashaku"

  
Worksheets("Tilaus").Activate 'Palauttaa etsinnän päätteeksi Tilaus taulukon näkymän


End Sub


Private Sub cmdLisää_asiakas_Click()


ThisWorkbook.Save


Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Asiakkaat")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
Dim Response

Response = MsgBox("TALLENNETAANKO UUDET ASIAKASTIEDOT?", vbYesNo, "ASIAKASKORTTI")
If Response = vbNo Then

Exit Sub
End If


'----------------------------------------------------------------

sh.Range("A" & last_row + 1).Value = "=row()-1" 'Asiakkaat taulun sarake A, jossa koodi rivi()-1. Luo uuden rivinron lisätessä asiakasta!
sh.Range("C" & last_row + 1).Value = Me.txtAsiakasnimi.Value
sh.Range("D" & last_row + 1).Value = Me.txtAsiakasnimi2.Value
sh.Range("E" & last_row + 1).Value = Me.txtAsiakasosoite.Value
sh.Range("F" & last_row + 1).Value = Me.txtAsiakaspostinro.Value
sh.Range("G" & last_row + 1).Value = Me.txtAsiakaskaupunki.Value
sh.Range("J" & last_row + 1).Value = Me.txtEmail.Value
sh.Range("K" & last_row + 1).Value = Me.txtPuhelin.Value

'-----------------------------------------------------------




MsgBox " ASIAKASTIEDOT TALLENNETTU "



Call lstHakuTulokset_AfterUpdate






End Sub

Private Sub cmdPäivitä_asiakas_Click()

ThisWorkbook.Save

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Asiakkaat")
Dim selected_Row As Long
selected_Row = Application.WorksheetFunction.Match(CLng(Me.txtRivinro.Value), sh.Range("A:A"), 0)
Dim Response

Response = MsgBox("PÄIVITETÄÄNKÖ ASIAKASTIEDOT?", vbYesNo, "ASIAKASKORTTI")
If Response = vbNo Then
Exit Sub
End If




'----------------------------------------------------------------

sh.Range("C" & selected_Row).Value = Me.txtAsiakasnimi.Value
sh.Range("D" & selected_Row).Value = Me.txtAsiakasnimi2.Value
sh.Range("E" & selected_Row).Value = Me.txtAsiakasosoite.Value
sh.Range("F" & selected_Row).Value = Me.txtAsiakaspostinro.Value
sh.Range("G" & selected_Row).Value = Me.txtAsiakaskaupunki.Value
sh.Range("J" & selected_Row).Value = Me.txtEmail.Value
sh.Range("K" & selected_Row).Value = Me.txtPuhelin.Value

'----------------------------------------------------------------





MsgBox "ASIAKASTIEDOT PÄIVITETTY "


Call lstHakuTulokset_AfterUpdate



End Sub


Private Sub cmdTyhjennä_asiakas_Click() '(LISÄÄ PUUTTUVAT TIEDOT)
Me.txtAsiakasnimi.Value = ""
Me.txtAsiakasnimi2.Value = ""
Me.txtAsiakasosoite.Value = ""
Me.txtAsiakaspostinro.Value = ""
Me.txtAsiakaskaupunki.Value = ""
Me.txtPuhelin.Value = ""
Me.txtEmail.Value = ""
Me.txtViite.Value = ""
Me.txtTilausnro.Value = ""
Me.txtTilaaja.Value = ""
Me.txtToimitusnimi.Value = ""
Me.txtToimitusnimi2.Value = ""
Me.txtToimitusosoite.Value = ""
Me.txtToimituspostinro.Value = ""
Me.txtToimituskaupunki.Value = ""
Me.txtLaskutusnimi.Value = ""
Me.txtLaskutusnimi2.Value = ""
Me.txtLaskutusosoite.Value = ""
Me.txtLaskutusPostinro.Value = ""
Me.txtLaskutuskaupunki.Value = ""



End Sub



Private Sub CommandButton2_Click()
Unload Me
UserForm1.Show

Me.txtAsiakasnimi.Value = ""
Me.txtAsiakasnimi2.Value = ""
Me.txtAsiakasosoite.Value = ""
Me.txtAsiakaspostinro.Value = ""
Me.txtAsiakaskaupunki.Value = ""
Me.txtPuhelin.Value = ""
Me.txtEmail.Value = ""
Me.txtViite.Value = ""
Me.txtTilausnro.Value = ""
Me.txtTilaaja.Value = ""
Me.txtToimitusnimi.Value = ""
Me.txtToimitusnimi2.Value = ""
Me.txtToimitusosoite.Value = ""
Me.txtToimituspostinro.Value = ""
Me.txtToimituskaupunki.Value = ""
Me.txtLaskutusnimi.Value = ""
Me.txtLaskutusnimi2.Value = ""
Me.txtLaskutusosoite.Value = ""
Me.txtLaskutusPostinro.Value = ""
Me.txtLaskutuskaupunki.Value = ""
End Sub

Private Sub lstHakuTulokset_AfterUpdate()

'HAKULISTA ASIAKKAAT


' Tämä näyttää lomakkeella tiedot myös tekstikentissä, kun haku suoritettu ja valittu listalta kone

Dim ws As Worksheet
Dim iRow As Long

Set ws = ThisWorkbook.Worksheets("Asiakashaku")
With Me
     iRow = Me.lstHakuTulokset.ListIndex + 2
     Me.txtRivinro.Value = Me.lstHakuTulokset.List(Me.lstHakuTulokset.ListIndex, 0)
    .txtAsiakasnimi.Value = ws.Cells(iRow, 3) 'Sarake C taulukossa
    .txtAsiakasnimi2.Value = ws.Cells(iRow, 4) 'Sarake D taulukossa
    .txtAsiakasosoite.Value = ws.Cells(iRow, 5) 'Sarake E taulukossa
    .txtAsiakaspostinro.Value = ws.Cells(iRow, 6) 'Sarake F taulukossa
    .txtAsiakaskaupunki.Value = ws.Cells(iRow, 7) 'Sarake G taulukossa
    .txtEmail.Value = ws.Cells(iRow, 10) 'Sarake J taulukossa
    .txtPuhelin.Value = ws.Cells(iRow, 11) 'Sarake K taulukossa

   
End With


End Sub

Private Sub lstHakuTulokset_Click()

End Sub

Private Sub txtPoistaAsiakas_Click()
'POISTA ASIAKASTIEDOT REKISTERISTÄ

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Asiakkaat")
Dim selected_Row As Long
selected_Row = Application.WorksheetFunction.Match(CLng(Me.txtRivinro.Value), sh.Range("A:A"), 0)
Dim Response

Response = MsgBox("HALUATKO VARMASTI POISTAA ASIAKASTIEDOT?", vbYesNo, "ASIAKASKORTTI")
If Response = vbNo Then
Exit Sub
End If

'----------------------------------------
sh.Range("A" & selected_Row).EntireRow.Delete
'----------------------------------------
Me.txtAsiakasnimi.Value = ""
Me.txtAsiakasnimi2.Value = ""
Me.txtAsiakasosoite.Value = ""
Me.txtAsiakaspostinro.Value = ""
Me.txtAsiakaskaupunki.Value = ""
Me.txtToimitusnimi.Value = ""
Me.txtToimitusnimi2.Value = ""
Me.txtToimitusosoite.Value = ""
Me.txtToimituspostinro.Value = ""
Me.txtToimituskaupunki.Value = ""
Me.txtLaskutusnimi.Value = ""
Me.txtLaskutusnimi2.Value = ""
Me.txtLaskutusosoite.Value = ""
Me.txtLaskutusPostinro.Value = ""
Me.txtLaskutuskaupunki.Value = ""

Call lstHakuTulokset_AfterUpdate

MsgBox "ASIAKASTIEDOT POISTETTU"


End Sub

Private Sub UserForm_Activate()


cmdTila.List = Array("", "Avoin työ", "Työn alla", "Laskutettava", "Valmis")



Call lstHakuTulokset_AfterUpdate




End Sub




Private Sub UserForm_Initialize()
txtEtsi_asiakas.SetFocus
Worksheets("Asiakashaku").Range("A2:R10000").ClearContents
End Sub
 

Attachments

  • Titles in the textboxesJPG.JPG
    Titles in the textboxesJPG.JPG
    109 KB · Views: 14

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
"SIIRTYMÄ(Asiakashaku!$A$2;0;0;LASKE.A(Asiakashaku!$A:$A)-1;18)" Is not found in the code you submitted
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
328
pure vito
P
P
  • Question
DeferredDeliveryTime help
Replies
1
Views
216
dreid1011
dreid1011
X
  • Solved
When update the record the entries are saved in the columnheaders ^^
Replies
2
Views
292
xainthowell
X
X
  • Solved
Displayed numeric date instead of the entered format date
Replies
6
Views
337
hicksi
hicksi
X
  • Solved
It didn't change the forecolor of the textbox
Replies
4
Views
344
Fluff
Fluff

Forum statistics

Threads
1,215,051
Messages
6,122,871
Members
449,097
Latest member
dbomb1414

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