optimize vba code

  • Hi. I have a userform to update some excel data. I have plenty codes like this. Is there anyway to optimize following code? Thanks in advance.



  • Re: optimize vba code


    You can use a loop, like this:


    Code
    for i=1 to x
    if label & i = "" then
    Msgbox "please select..."
    elseif Btn & i = false then
    label & ii.enabled = true
    end if
    next


    I'm afraid my vba skills are not enough to provide you the full code, but I think this is the way to go.

  • Re: optimize vba code


    Thanks for answering. I think your way is true. But real issue is I have button for each label. Loop must be triggered by assigned button. Please help.

  • Re: optimize vba code


    Quote from Krishnakumar;770250

    Probably a Class Module would help you.


    Thanks but there are a lot examples like that. They perform only Btn_click part. İ tried to combine buttons with labels but i couldnt.

  • Re: optimize vba code


    Hi again. I solved the problem by these people's book: John Green, Stephen Bullen, Rob Bovey, Michael Alexander (Excel 2007 VBA Programmer's Reference)


    The trick for class module which Krishnakumar mentioned is:


    Code
    Public WithEvents ButtonName As MSForms.CommandButton
    Private Sub ButtonName_Click()
    Dim sButtonC, sComboboxN As String
    sButtonC = ButtonName.Caption
    sComboboxN = "cbx" & sButtonC
    With UserForm1.Controls(sComboBoxN)
    .Enabled = True
    End With
    End Sub




    Thanks for your answers.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!