• Úvod
• Excel návod
• Excel šablony
• Nástěnka
• Mobility
• Tipy na výlet
• Galerie
• Fórum

Příspěvky uživatele

Zkuste verzi 2.
Nemam ceske office, OS. Nevim, jak to bude s formatem vysledku, hlavne sloupec C a D. Trosku mne to zlobilo. Dejte vedet.

syd

V priloze oprava vypoctu dane.

Dobry den,

vyzkousejte a dejte vedet.

dik
syd

Protoze jsme presunuli TextBoxes do Frame, tak je treba pozmenit (trochu krkolomne) proc OnlyNumbers:

Sub OnlyNumbers()
If TypeName(frmEvidence.ActiveControl.ActiveControl) = "TextBox" Then
With frmEvidence.ActiveControl.ActiveControl
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Pouze cisla...", , "Pozor"
.Value = vbNullString
End If
End With
End If
End Sub

Vyzkousejte a dejte vedet.
Dik
syd

Tak tedy cely kod nasledovne:

Sub vyplnFormular(Riadok As Long)
Dim vBColor, vFColor, bBold As Boolean
Dim i As Integer, x As Integer
With UserForm1
With .TextBox1
.Value = Cells(ActiveCell.Row, 1)
.BackColor = Cells(ActiveCell.Row, 1).Interior.Color
.ForeColor = Cells(ActiveCell.Row, 1).Font.Color
End With
With .TextBox2
.Value = Cells(ActiveCell.Row, 2)
.BackColor = Cells(ActiveCell.Row, 2).DisplayFormat.Interior.Color
.Font.Bold = Cells(ActiveCell.Row, 2).DisplayFormat.Font.Bold
.ForeColor = Cells(ActiveCell.Row, 2).DisplayFormat.Font.Color
End With
With .TextBox3
.Value = Format(Cells(ActiveCell.Row, 3), "dd.mm.yy")
.BackColor = Cells(ActiveCell.Row, 3).Interior.Color
.ForeColor = Cells(ActiveCell.Row, 3).Font.Color
End With
With .inkBox
.Text = Cells(ActiveCell.Row, 4)
i = Len(Cells(ActiveCell.Row, 4))
For x = 1 To i
.SelStart = x - 1
.SelLength = 1
.SelColor = Cells(ActiveCell.Row, 4).Characters(x, 1).Font.Color
Next x
End With
End With

Misto textbox4 je pouzit InkEdit control viz priloha.
syd

Zkuste tohle:
.TextBox2.BackColor = Cells(ActiveCell.Row, 2).DisplayFormat.Interior.Color
.TextBox2.Font.Bold = Cells(ActiveCell.Row, 2).DisplayFormat.Font.Bold
.TextBox2.ForeColor = Cells(ActiveCell.Row, 2).DisplayFormat.Font.Color


ohledne bodu 3 zkuste prostudovat nasledujici:
http://stackoverflow.com/questions/23598211/every-character-in-textbox-in-different-color-in-vba-excel

Podminene fomatovani bunky se do Textboxu neprenese.. je potreba manualne nastavit v podmince jako v samotnem podm. form. napr:


Sub vyplnFormular(Riadok As Long)
Dim vBColor, vFColor, bBold As Boolean
With UserForm1
.TextBox1.Value = Cells(ActiveCell.Row, 1)
.TextBox2.Value = Cells(ActiveCell.Row, 2)
.TextBox3.Value = Format(Cells(ActiveCell.Row, 3), "dd.mm.yy")

.TextBox1.BackColor = Cells(ActiveCell.Row, 1).Interior.Color
.TextBox1.ForeColor = Cells(ActiveCell.Row, 1).Font.Color

Select Case Cells(ActiveCell.Row, 2).Value
Case Is = "B4"
vBColor = RGB(153, 204, 0)
vFColor = &H80000008
bBold = False
Case Is = "B3"
vBColor = &HFFFF&
vFColor = &HFF0000
bBold = True
Case Else
vBColor = &H80000005
vFColor = &H80000008
bBold = False
End Select

.TextBox2.BackColor = vBColor
.TextBox2.ForeColor = vFColor
.TextBox2.Font.Bold = bBold

.TextBox3.BackColor = Cells(ActiveCell.Row, 3).Interior.Color
.TextBox3.ForeColor = Cells(ActiveCell.Row, 3).Font.Color

End With
End Sub

syd

Prikladam verzi 2 pro predstavu, jak udelat.
Musel jsem to trochu zmenit.
Nejdrive pridavam control Frame a do neho se pak skladaji Labels a TextBoxes.
Kdyz je polozek vic jak 15, 'zmrazi' se vyska Frame (i formulare), a zobrazi se scrollbar ve Frame.

syd

Zdravim,

taky jsem zkousel... s UserForm moc nedelam. Tak muzete vyzkouset muj pokus.
Samozrejme jsem taky musel trochu sesit upravit, a nemam ceske office.. tudiz bez diakritiky.

Ahoj,
viz priloha.
syd

.. a jak zjistim, jakou ma tovar expiracii??? V tabulce to nevidim... vloz do druheho listu radky, kt. by po makru meli byt zkopirovany... nebo poradne popis, co je treba provest.

dik
syd

Tady jeste jedno nakopnuti...
Lepsi reseni nez prve makro (kt. bych radsi smazal, ale nejde to..):


Sub test1()
Dim ws As Worksheet, a, el, lr As Long, target As Range
Application.ScreenUpdating = 0
Set ws = ActiveSheet
a = ws.Range("F3:I3")
lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Set target = ws.Range("F4")
For Each el In a
With ws.Range("A3:B" & lr)
.AutoFilter field:=1, Criteria1:=el
.Resize(.Rows.Count - 1, 2).Offset(1, 0).Columns(2).Copy
target.PasteSpecial xlPasteValues
.AutoFilter
Set target = target.Offset(0, 1)
End With
Next el
Application.ScreenUpdating = 1
End Sub

Zkuste:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, s As String
Set rng = ThisWorkbook.Sheets(1).Range("A1:A20")
If Not Intersect(Target, rng) Is Nothing Then
If Target.Rows.Count = 1 Then
s = Target
Application.EnableEvents = 0
rng.ClearContents
Target = s
Application.EnableEvents = 1
End If
End If
End Sub


syd

Uživatelské menu

Nejste přihlášen(a)

• přihlásit
• registrace

\n

Menu

On-line nástroje

Formulář Faktura

Aktivní diskuse