Posts by slapman

Re: Getting a cell value displayed in textbox from Combox

Quote from StephenR;787900

We don't need to see actual dta, dummy data will do.

sorry
i just cannot.

i figured some codding for second part but im getting : end if without block if , why?

Private Sub Uf2update_Click()
     Dim ctl As Control
     Dim i As Long
     Dim Rng As Range
     Dim FindString As String
     
     FindString = CB1.Value
    
     For Each ctl In Me.Controls
          If TypeName(ctl) = "textm" Then
                
                For i = 2 To 2
               If Not IsEmpty(Me.Controls("textm" & i)) And Not Len(Trim(Me.Controls("textm" & i).Value)) = 0 And (Me.Controls("textm" & i).Value) <> 0 Then
                 
                     With Sheets("S5").Range("A:A")
                             Set Rng = .Find(What:=FindString, _
                                   After:=.Cells(.Cells.Count), _
                                   LookIn:=xlValues, _
                                  LookAt:=xlWhole, _
                                  SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
                                    
                                    Application.Goto Rng.Offset(0, -8) = textm2.Value
                            
                            
                                 
                                End If
                            Next i
                        
                    Next ctl
    Unload Me
End Sub

Re: Getting a cell value displayed in textbox from Combox

I will elaborate my problem and hope you guys can help me.

i have in A UserForm : 1 X combo box , 2x Textboxes

Combox is populting values from another sheet :

Private Sub UserForm_Initialize() 
 Me.CB1.List = Worksheets("s5").Range("a2:a342").Value
End Sub

when i choose a value in the combo box #1 textbox will show its value.
that is being done using :

Private Sub CB1_Change()
Dim name As String
name = CB1.Value
Value = Application.WorksheetFunction.VLookup(name, S5.Range("A2:F341"), 5, False)
Me.textm1.Value = Format(Value, ".00")
End Sub

The other textbox (called textm2) is for the End User to type in a diffrent value to change, if he would like to ,when observing the value given from Textbox1 (textm1).

Now....

As im populating values to combo box from

Worksheets("s5").Range("a2:a342")

and showing values from E2:E342 using Vlookup

Value = Application.WorksheetFunction.VLookup(name, S5.Range("A2:F341"), 5, False)

The problem is:

How to populate The user input From textbox2 (=textm2) , 3 columns (="H2:H342") after the values populated from "E2:E342" in textbox1 , According to the selected Value from combox and place it the the correct row and the correct column.

Re: Getting a cell value displayed in textbox from Combox

i cant post the workbook due to confidential data
but i can answer any question that can help solving my issue

Re: Getting a cell value displayed in textbox from Combox

update :

solved issue 1 using vlookup in vba code

Private Sub CB1_Change()Dim name As String
 name = CB1.Value
Value = Application.WorksheetFunction.VLookup(name, S5.Range("A2:F341"), 5, False)
Me.textm1.Value = Value
End Sub

Re: Getting a cell value displayed in textbox from Combox

thx stephenR , but it doesnt work - nothing happens , looks like its not what i need.

let me be more clear.

textbox1 = show the actual value of the selected item from combo box. so if i chose J26 (located in A59) i will get its value 16.9 (located in E59)

textbox2 = the user which uses the sheet maybe would like to currect the number (from 16.9) to what ever he wants to add , the number he will insert will be copied to cell H59.

hope thats better.

Hey ,

I have created a small userform with 1 combo box 2 textboxes.

the combobox displaying range of values from a given range:

Private Sub UserForm_Initialize()  
Me.CB1.List = Worksheets("s5").Range("a2:a342").Value
End Sub

one text box should display the value from the current cell which selected by the listbox.

second text box is using as an input box to place a value in the next column.

for example : i choose in list box (values at a2:a342) the value "J26" , textbox1 (values at e2:e342) will show current value for J26 which is 16.9 , textbox2 will be used by the user to enter a value that will be copied to a cell in column h (values will be implant at H2:H342)

hope i explained myself correctly , ill any appriciate help for it

thanks alot.

Re: flag empty value in textbox

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

thanks again carim

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 *****

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

Re: Search S/N from text box running by Cmd Button

thanks carim ,

i solved my problem.8-)

Hello again.

I need the ability to search in current active sheet S/n which i enter in a textbox sitting in the top of the current activesheet.
data sheet called "S2".
all S/N located at A6:A50000

User using the sheet will input S\N in the TextBox ("txtsearch") and will click a command button to search.

result should be: vba code will find the S/n
1. if there is a match , will prompt "match found" with msg box
2. after click Ok on msg box , vba code will give focus on the found cell.

if nothing found: simple msg box with : "no s/n found".

so far i got this but im lost with debbuger saying things like " out of range" , "overflow" everytime im trying .
hope you guys can help me again.

Option Explicit


Private Sub FindButton_Click()
    Dim ws As Worksheet
    Dim FindString As String
    Dim Rng As Range




    Set ws = ThisWorkbook.Worksheets("S2")




    FindString = txtsearch.Value




    If Trim(FindString) <> "" Then
        Set Rng = ws.Cells.Find( _
                         What:=FindString, _
                         LookIn:=xlValues, _
                         LookAt:=xlPart, _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, _
                         MatchCase:=False, _
                         SearchFormat:=False)




        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End If




End Sub

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!

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.

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.

Re: flag empty value in textbox

the revised code now wont give any debugger error but it put the computer into loop and eventualy allow the target cells here (s1.Cells(i, 6)) to change to blank or 0 incase all textboxes (43-56) are left blank or 0

Re: flag empty value in textbox

whish i could post my workbook i cant for confidentiality reasones

any ways ,debugger indicates on the "****" line i marked :

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 Len(Trim(ctl(i + 41).Value)) = 0 Then   *******
                    s1.Cells(i, 6) = ctl(i + 41).Value
                End If
            Next i
        End If
    Next ctl
    Unload Me
















End Sub

Re: flag empty value in textbox

Quote from Carim;786748

Hello,

The loop does the input after the check ... :wink:

So ... as long as your Userform is called uf1 and your worksheet is called s1 ...

the code should do the job ...

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 Len(Trim(ctl(i + 41).Value)) = 0 Then 
                    s1.Cells(i, 6) = ctl(i + 41).Value 
                End If 
            Next i 
        End If 
    Next ctl 
    Unload Me 
End Sub

Display More

Display More

I revised the cmd button to that above.

still getting that error

[ATTACH=CONFIG]71554[/ATTACH][ATTACH=CONFIG]71555[/ATTACH]

excuse me for my Noobiness

Re: flag empty value in textbox

carim thank you so much for helping me again.

Private Sub Updateb1_Click()




s1.Range("F2") = uf1.TextBox43.Value
s1.Range("F3") = uf1.TextBox44.Value
s1.Range("F4") = uf1.TextBox45.Value
s1.Range("F5") = uf1.TextBox46.Value
s1.Range("F6") = uf1.TextBox47.Value
s1.Range("F7") = uf1.TextBox48.Value
s1.Range("F8") = uf1.TextBox49.Value
s1.Range("F9") = uf1.TextBox50.Value
s1.Range("F10") = uf1.TextBox51.Value
s1.Range("F11") = uf1.TextBox52.Value
s1.Range("F12") = uf1.TextBox53.Value
s1.Range("F13") = uf1.TextBox54.Value
s1.Range("F14") = uf1.TextBox55.Value
s1.Range("F15") = uf1.TextBox56.Value




 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 Len(Trim(ctl(i + 41).Value)) = 0 Then
                    s1.Cells(i, 6) = ctl(i + 41).Value
                End If
            Next i
        End If
    Next ctl
    Unload Me
















End Sub

and got:

[ATTACH=CONFIG]71553[/ATTACH]

what im doing wrong?

Re: flag empty value in textbox

Quote from Carim;786674

It would seem you have tackled a new issue ...no ???

Have you sorted out the issue related to preventing users from adding spaces in their inputs ???

hey carim , that was never the issue, maybe i failed to explain myself better.

My finale issue im dealing now is to "check" the textboxes to see that all of them have any value <0 (= No blanks in the textboxes) while im pressing command button that supposed to unload the form.

"PrivateSub Updateb1_Click() "

i made that command button unavaliable but i need a code that check all the boxes i have from TextBox43 to Textbox56 with value >0 then allow command button to be available to unload the form while during this action Cells updating with
the values entered in the text boxes.

Re: flag empty value in textbox

my problem is that my update button will change cells values according what entered inside the textboxes. thats only thing it will do

i need a sulotion to prevent that update button to be active if any of the textboxs containing blanks

code per textbox (there are 20-30 boxes):

Private Sub TextBox43_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case Asc("0") To Asc("9")
    Case Asc("-")
        If InStr(1, Me.TextBox43.Text, "-") > 0 Or Me.TextBox43.SelStart > 0 Then
            KeyAscii = 0
        End If
    Case Asc(".")
        If InStr(1, Me.TextBox43.Text, ".") > 0 Then
            KeyAscii = 0
        End If
    Case Else
        KeyAscii = 0
End Select
End Sub

thats the code for the command button:

Private Sub Updateb1_Click()




s1.Range("F2") = uf1.TextBox43.Value




s1.Range("F3") = uf1.TextBox44.Value




s1.Range("F4") = uf1.TextBox45.Value




s1.Range("F5") = uf1.TextBox46.Value




s1.Range("F6") = uf1.TextBox47.Value




s1.Range("F7") = uf1.TextBox48.Value




s1.Range("F8") = uf1.TextBox49.Value




s1.Range("F9") = uf1.TextBox50.Value




s1.Range("F10") = uf1.TextBox51.Value




s1.Range("F11") = uf1.TextBox52.Value




s1.Range("F12") = uf1.TextBox53.Value




s1.Range("F13") = uf1.TextBox54.Value




s1.Range("F14") = uf1.TextBox55.Value




s1.Range("F15") = uf1.TextBox56.Value








Unload Me




End Sub

Re: flag empty value in textbox

thank you carim!
only thing not resolved is that blank is acceptable
there is any case that can be filled into the code that will prevent blank?
or another procedureshould be added?