Several VBA Userforms Questions including formulas in the Userform and uneditable textboxes.

[Xalova]

Ok. I am fairly new to the whole userform thing, but i find it really useful for what i want to do with it. But i still got several questions about it.

1. I have managed toget a fairly big userform to work and enter all those values in a table. But i figured out that when i use the userform to enter Data in the table, it wont copy the existing formulas in the new row. Which it should, since its a table, no?

2. is it possible to get a "uneditable" textbox in a userform which just displays the value of a formula from numbers entered in the same userform textboxes? Is there such a thing as live calculation inside a userform?

3. Can i dim variables in a if statement? "if variable is empty then dim as string else dim as number"? I need this since when i try to leave a txtbox blank which has the property "dim as double/interger/long" i get the errer that the datatypes dont match.

thats it for now... i hope that i can get some help

 

[dmt32]

Hi,

1 - without seeing the code behind the form that posts data to the table its difficult to give an answer why it does not work in the required manner

2 - Yes, just set the controls Locked property to True & this will stop users editing the displayed text

Me.TextBox1.Locked = True

3 - Be mindful what comes out of a textbox is text so if working with numeric values good idea to Coerce the text to the required data type using one of the built-in functions.
In Most cases, the Val function is the kindest to use as it stops reading the string at the first character that it can't recognize as part of a number and just returns the numeric value also if blank, will just return 0 (zero) which should avoid the the type mismatch error.

Dim myvar As Long
myvar = Val(Me.TextBox1.Value)

you can read more here: Val function (Visual Basic for Applications)

If need further help post the code behind your form or better still, place copy of the workbook with dummy data on a file sharing site like dropbox & provide a link to it. Plenty here to offer guidance.

Hope Helpful

Dave

 

[Xalova]

Hi,

1 - without seeing the code behind the form that posts data to the table its difficult to give an answer why it does not work in the required manner

2 - Yes, just set the controls Locked property to True & this will stop users editing the displayed text

Me.TextBox1.Locked = True

3 - Be mindful what comes out of a textbox is text so if working with numeric values good idea to Coerce the text to the required data type using one of the built-in functions.
In Most cases, the Val function is the kindest to use as it stops reading the string at the first character that it can't recognize as part of a number and just returns the numeric value also if blank, will just return 0 (zero) which should avoid the the type mismatch error.

Dim myvar As Long
myvar = Val(Me.TextBox1.Value)

you can read more here: Val function (Visual Basic for Applications)

If need further help post the code behind your form or better still, place copy of the workbook with dummy data on a file sharing site like dropbox & provide a link to it. Plenty here to offer guidance.

Hope Helpful

Dave

1 - yes, i will do my best to share a dummy file.

2 - thats awesome. but at the same time i want that same textbox to display a calculation, if that is even possible?

3 - how do i utilize that though?

i have the following code (just a dummy ofcourse)

Private Sub Enter_Click()
    Dim Menge As String
    Dim ws As Worksheet
    Set ws = Sheets("Aufstellung Verbrauchsmaterial")
        With ws
            'Menge
            Menge = Me.Menge.Value
        End With
Dim tbl As ListObject
Set tbl = ws.ListObjects("Verbrauchsmaterial")
Dim newrow As ListRow
Set newrow = tbl.ListRows.Add
With newrow
    .Range(3) = Menge

    End With
    Unload NeuerArtikel
End Sub

The textbox named "Menge", would need to be formatted as a number. If i try to say "dim as double/integer" it would give me an error if i leave it blank.
how do i utilize your solution?

 

[RoryA]

You can do something like:

If IsNumeric(menge) then .Range(3).Value2 = CDbl(Menge)

 

[Xalova]

You can do something like:

If IsNumeric(menge) then .Range(3).Value2 = CDbl(Menge)

in that case, what is "CDbl(Menge)"? is that my textbox? what does the "CDbl" stand for?

 

[dmt32]

The textbox named "Menge", would need to be formatted as a number. If i try to say "dim as double/integer" it would give me an error if i leave it blank.
how do i utilize your solution?

I provided an example how to use the Val function - Just change the name of textbox1 shown in bold as required

Dim myvar As Long
myvar = Val(Me.TextBox1.Value)

Or if using another function you can use the approach suggested by @RoryA

Dave

 

[RoryA]

what does the "CDbl" stand for

It's a function that coerces numeric text to a true number using your regional settings. (Val is quite limited in terms of what it will understand as a numeric string).