flag empty value in textbox

slapman · Mar 1st 2017

Re: flag empty value in textbox

dont know why but its not working.
if i leave every textbox empty and just press the Updateb1_Click() then all the targeted cells turns to 0
also these targetd cells wont update even if i put in number bigger then 0.

**Carim** · Mar 1st 2017

Re: flag empty value in textbox

Another attempt

Code

Private Sub Updateb1_Click() 
    Dim ctl As Control 
    Dim i As Long 
    For Each ctl In Me.Controls 
        If TypeName(ctl) = "TextBox" Then 
            For i = 2 To 15 
                If Not IsEmpty(Me.Controls("TextBox" & i + 41)) And Not Len(Trim(Me.Controls("TextBox" & i + 41).Value)) = 0 Then 
                    s1.Cells(i, 6) = Trim(Me.Controls("TextBox" & i + 41).Value)
                End If 
            Next i 
        End If 
    Next ctl 
    Unload Me 
End Sub

Display More

Shame is ... I cannot perform any tests ...

slapman · Mar 1st 2017

Re: flag empty value in textbox

it works partly , crazy runtime though looks like the loop is really heavy on it.
sorry i cannot attach the worksheet as info is sensitive.

**Carim** · Mar 1st 2017

Re: flag empty value in textbox

Crazy runtime can be related to many issues ... including the size of your workbook, the complexity of your userform, etc ....

Take a look at the ten tip to speed up your macros ...

http://datapigtechnologies.com/blog/index.php/ten-things-you-can-do-to-speed-up-your-excel-vba-code/

slapman · Mar 1st 2017

Re: flag empty value in textbox

Quote from Carim;786780

Crazy runtime can be related to many issues ... including the size of your workbook, the complexity of your userform, etc ....

Take a look at the ten tip to speed up your macros ...

http://datapigtechnologies.com…d-up-your-excel-vba-code/

carim!
thank you for all your help so far
i used the Application.Calculation = xlCalculationManual

and its now works flawlessly.

ill do few more tests and will update. thanks again!

**Carim** · Mar 1st 2017

Re: flag empty value in textbox

Glad you could progress on your project ...:wink:

Thanks for ... your thanks ...:smile:

All the Best for the next steps !!!

slapman · Mar 5th 2017

Re: flag empty value in textbox

carim another maybe simple issue i have here

i also now have textbox1 to textbox14 to add values which supposed to do the same action , and now plant the values the column before ( s1.Cells(i, 5)) how to do this?

i thought i could do by same idea only call another variable with negative value - but it wont work on the line i marked *****

Code

Private Sub Updateb1_Click()Application.Calculation = xlCalculationManual
    Dim ctl As Control
    Dim i As Long
    Dim i2 As Long
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            For i = 2 To 15
                If Not IsEmpty(Me.Controls("TextBox" & i + 41)) And Not Len(Trim(Me.Controls("TextBox" & i + 41).Value)) = 0 And (Me.Controls("TextBox" & i + 41).Value) <> 0 Then
                    s1.Cells(i, 6) = Trim(Me.Controls("TextBox" & i + 41).Value)
                End If
           
            For i2 = -40 To -26
                If Not IsEmpty(Me.Controls("TextBox" & i + 41)) And Not Len(Trim(Me.Controls("TextBox" & i + 41).Value)) = 0 And (Me.Controls("TextBox" & i + 41).Value) <> 0 Then ********************
                    s1.Cells(i, 5) = Trim(Me.Controls("TextBox" & i + 41).Value)
                End If
                Next i, i2
              
        End If
    Next ctl
    Unload Me
    
Application.Calculation = xlCalculationAutomatic








Dim pt As PivotTable








    Set pt = ActiveSheet.PivotTables("PVT1")




    pt.RefreshTable








End Sub

Display More

**Carim** · Mar 5th 2017

Re: flag empty value in textbox

Hello,

You could test following

Code

Private Sub Updateb1_Click()
Application.Calculation = xlCalculationManual
    Dim ctl As Control
    Dim i As Long
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Then
            For i = 2 To 15
                If Not IsEmpty(Me.Controls("TextBox" & i + 41)) And Not Len(Trim(Me.Controls("TextBox" & i + 41).Value)) = 0 And (Me.Controls("TextBox" & i + 41).Value) <> 0 Then
                    s1.Cells(i, 6) = Trim(Me.Controls("TextBox" & i + 41).Value)
                End If
            Next i
                 
            For i = 1 To 14
                If Not IsEmpty(Me.Controls("TextBox" & i)) And Not Len(Trim(Me.Controls("TextBox" & i).Value)) = 0 And (Me.Controls("TextBox" & i).Value) <> 0 Then
                     s1.Cells(i, 5) = Trim(Me.Controls("TextBox" & i).Value)
                End If
            Next i
        End If
    Next ctl
    Unload Me
Application.Calculation = xlCalculationAutomatic
End Sub

Display More

HTH

slapman · Mar 5th 2017

Re: flag empty value in textbox

LOL. im looking at your coding and feeling embraced for the noob sulotion i wrote

thanks again carim

**Carim** · Mar 5th 2017

Re: flag empty value in textbox

You are welcome ...:wink:

" Every step teaches us to learn something New ... " :smile:

flag empty value in textbox

Participate now!

Share

Users Viewing This Thread