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

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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 :)
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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.

VBA Code:
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

Board Regular
Joined
Feb 11, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

VBA Code:
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.

VBA Code:
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)

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,706
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can do something like:

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

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

You can do something like:

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
Dim myvar As Long
myvar = Val(Me.TextBox1.Value)

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

Dave
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,706
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,150
Members
417,010
Latest member
jnuss03

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
Top