Posts by Michaelscp

Hello,

you mean that i put the activate code instead of initialize? dont works - maybe the code dont compare with the other code in worksheets.

ActiveSheet.Protect userinterfaceonly:=False

 Dim objCell As Range
    For Each objCell In Range(Cells(3, 18), Cells(Rows.Count, 18).End(xlUp))
        If objCell.Value >= TimeSerial(0, 0, 10) Then
            If IsEmpty(objCell.Offset(0, 1).Value) Then
                With UserForm1
                    Set .Cell = objCell.Offset(0, 1)
                    If Not .Visible Then Call .Show
                    Call AppActivate(Application.Caption)
                    Exit For
                End With
            End If
        End If
    Next
End Sub

Thats the code of the userform:

Option Explicit

Private mobjCell As Range

Private Sub CommandButton1_Click()
    Call Hide
End Sub


Private Sub CommandButton2_Click()
    If TextBox1 <> "" Then
        Cell.Value = TextBox1.Text
    Else
        Call MsgBox("Bitte erst eine Begründung eingeben.", vbExclamation, "Hinweis")
    End If
    Call Hide
End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Activate()
   Me.Tag = Val(Me.Tag) + 1
   CommandButton1.Caption = "Hide (" & CStr(Me.Tag) & ")"
   With TextBox1
      .Text = ""
      .SetFocus
   End With
End Sub


Private Sub UserForm_Terminate()
    Set Cell = Nothing
End Sub

Public Property Get Cell() As Range
    Set Cell = mobjCell
End Property

Public Property Set Cell(ByRef probjCell As Range)
    Set mobjCell = probjCell
End Property

Hello,

maybe you can tell me where to write the unload comand? can i just write it before the "show" or somewhere else?

is it possible to activate a usb connected "light" when the userform is open? so that its visible that there is an action needed? can i do this also with a vba code?

thank you

i would say that unload/show would be the better solution.

now it works good that i write my reason in the textbox, say ok and it writes in the excel. but if the userform loads again it is the allready filled.

so it would be good to unload it and show it then, i think.

if i write the vbNullString i get an error from "cell.value = TextBox1.Text - maybe because i would write the reason from the textbox in a field and not to empty before close.

is it not possible to empty the textbox always before opening?

i understand what do you mean - but how do i do that?

maybe that works:

32.22_F22_Test_2505 (7) (1).xlsm

shared with file.io - super simple file sharing

file.io

the filesize is 27mb so i can not make an attachments. If i click on the link i get to the download file strange

i loaded one testfile in transfernow, hope this works. maybe you can take a look at it.

or is there an other way to load up a file here?

Maybe i show you the code of the userform:

 Dim objCell As Range
    For Each objCell In Range(Cells(3, 18), Cells(Rows.Count, 18).End(xlUp))
        If objCell.Value >= TimeSerial(0, 0, 10) Then
            If IsEmpty(objCell.Offset(0, 1).Value) Then
                With UserForm1
                    Set .Cell = objCell.Offset(0, 1)
                    If Not .Visible Then Call .Show
                    Call AppActivate(Application.Caption)
                    Exit For
                End With
            End If
        End If
    Next
End Sub

The userform opens for the timeserial. I write then the reasoncode in it (or write it with the barcode). After the "ok" button, the reasoncode will be written in the excel and the userform get closed.

Hello,

the first time of the day, when the userform opens the textbox is empty. After i set the first reasoncode and safe it. The next time when the userform opens this reasoncode allready is in the textbox, so i have to delete it.

I want that the textbox is always empty if the userform opens.

br

Good Morning,

its my first message in here. I am from austria and work in switzerland as a detail planner. We created a tool in excel where we can count the pieces in production and we see which failures we have and how much time we loose. At the end the calculation will give us an oee. Its the cheap version if you dont want to buy a running system for that

Now i have a question for a code in vba, we created a userform to definite which failure we have.

Our sensor writes always a 1 the machine produces. if not there is nothing. For example, we dont produce 30min, after that we start producing and the sensor write 1. Then the userform pop up and want that we say, why we could not produce the last 30min.

First there was a combobox with a list - but now we decided to change that in a normal textbox and scan a barcode, so the reason will be in the textorm.

So far so good - that works, and the reason will also be in the excel then.

BUT: at the next time when the userform open the textform is allready filled with this reason code - but i want that it is always empty, when it opens.

I am sure, there will be a very simple solution, but i cant find it

This is the code of the userform:

Option Explicit

Private mobjCell As Range

Private Sub CommandButton1_Click()

Call Hide

End Sub

Private Sub CommandButton2_Click()

If TextBox1 <> "" Then

Cell.Value = TextBox1.Text

Else

Call MsgBox("Bitte erst eine Begründung eingeben.", vbExclamation, "Hinweis")

End If

Call Hide

End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Initialize()

TextBox1.Text = ""

End Sub

Private Sub UserForm_Terminate()

Set Cell = Nothing

End Sub

Public Property Get Cell() As Range

Set Cell = mobjCell

End Property

Public Property Set Cell(ByRef probjCell As Range)

Set mobjCell = probjCell

End Property

I hope you someone can help me - i am not sure if this must be in the code of the userform or in the worksheet code?!

Thank you all and br

Michael