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: 15

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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

G
  • Solved
Run-Time Error 381
2
Replies
12
Views
197
Micron
M
G
  • Question Question
Cannot create a new time-in entry
Replies
2
Views
184
genesix
G
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
612
pure vito
P
J
  • Solved
Hide Buttons Selectively
Replies
3
Views
92
dmt32
D
J
  • Solved
VBA 3 Column Table and Display each column separately in 3 list boxes.
Replies
2
Views
267
JulianvO
J

Forum statistics

Threads
1,224,531
Messages
6,179,379
Members
452,907
Latest member
Roland Deschain

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