Related questions

Excel VBA code to disable four buttons in order of priority

2022-04-19 12:53 Atobseg imported from Stackoverflow

There are four buttons to click to take four sample weights. The buttons are B1,B2,B3 and B4. B1 must be clicked first, followed by B2, then B3 and lastly B4 in that order of priority. I want to write an excel VBA code for below senario: (1)B2, B3 and B4 must be disabled until B1 is clicked and (2)B3 and B4 must be disabled until B2 is clicked and (3)B4 must be disabled until B3 is clicked

Any idea would help.

1 answer

  • answered 2022-04-19 13:35 Attila

    One approach is to enable/disable the button - you add 4 buttons, assign the event Button1_Click to Button4_Click and use Button1.Enabled - Button4.Enabled. Please note that Enabled does not turn off the events assigned to the button. Also .Enabled will not change the color, it's better to use your own handler for that. What I would do is to add the 4 buttons, leave their names as they are (Button 1 to Button 4). Then use this somewhat generic solution:

    Option Explicit

Public Sub Button1_click()
    If Sheet1.Buttons(Application.Caller).enabled = False Then Exit Sub
    Dim btn As Button,arr As Variant,s%
    arr = Array(2, 3, 4, 1)
    s = Int(Split(Application.Caller, " ")(1)) - 1
    For Each btn In Sheet1.Buttons
        changeButtonStatus btn, btn.Name = "Button " & arr(s)
    Next btn
End Sub

Private Sub changeButtonStatus(ByVal btn As Button, ByVal enabled As Boolean)
    btn.Font.ColorIndex = IIf(enabled, 1, 15)
    btn.Enabled = enabled
End Sub

    What does it do? Creates an array of integers which point to the next button to be enabled. First is Button 2, second is Button 3, third is Button 4, fourth is Button 1. Then checks which button was pressed (Application.Caller), and retrieves the number at the end (by using Split function), and decrease it by one - because the array indexing starts at 0. Then changes the status - if the button is the next one, it enables it, else disables.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum

See also questions close to this topic

  • .Body & .HTMLBody Indent and Default Signature Issue

    I've set up a macro to send an e-mail through Outlook.

    .Body is read from a cell inside the file with indents. Since the value will change depending on the usage, I need to reference that cell for the body.

    However, there rises 2 issues using .HTMLbody I lose indents which are constructed with CHAR(10) but I keep the default HTML signature.

    When using just .BODY indents are displayed are correctly however the default signature is not constructed as HTML and I lose the images.

    How should I go about fixing this issue?

    My code:

    sig = .HTMLBody
body = xlSht.Range("B4").Value
    .To = xlSht.Range("B2").Value
    .CC = ""
    .Subject = xlSht.Range("B1").Value
    .body = body & sig
    .Display

    I'd really appreciate your assistance.

    Thanks.

  • Yahoo Finance no longer returns VBA cookie request for .getResponseHeader("Set-Cookie")

    The following Excel VBA code segment has worked for years, but stopped working around 28 Apr 2022. I receive the responseText, but the .getResponseHeader("Set-Cookie") returns null.

    Set httpReq = New WinHttp.WinHttpRequest

DownloadURL = "https://finance.yahoo.com/lookup?s=" & stockSymbol
With httpReq
    .Open "GET", DownloadURL, False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
    .Send
    .waitForResponse
    response = .responseText
    cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
End With
  • export excel rows to individual json files in python

    My excel file has 500 rows of data. I am trying to get 500 individual JSON files. Each file should have data only from 1 row. Thank you in advance.

    import json
import pandas
excel_data_df = pandas.read_excel("F:/2/N.csv.xlsx", 
sheet_name='Sheet1')
json_str = 
excel_data_df.to_json(orient='records')
for idx, row in enumerate(json_str):
fpath = str(idx) + ".json"
with open(fpath, "w+") as f:
json.dump(row, f)
  • VBA: Creating a class property that is an array of dictionaries

    In Microsoft Excel VBA I need to create a class that has two properties, "Name" and "Holdings". "Name" is just a string so the code for that is easy. But I need "Holdings" to be a variable length array containing dictionary objects. So this is what I wrote:

    Private mName As String
Private mHoldings() As Scripting.Dictionary

Public Property Let Name(vName As String)
      mName = vName
End Property
 
Public Property Get Name() As String
      Name = mName
End Property
 
Public Property Set Holdings(vHoldings() As Scripting.Dictionary)
      Set mHoldings = vHoldings
End Property
 
Public Property Get Holdings() As Scripting.Dictionary
      Set Holdings = mHoldings
End Property

    When I try to compile it gives me this error: "Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalide Set final parameter.

    What I doing wrong?

  • Sort the digits in the cell value using a VBA function

    Can any one help me with VBA code. I want to sort the number in ascending and descending order in excel but a single cell value. For instance, if user entered 5634 in "A1" cell i want the result in "A2" as "3456" (i.e ascending) and in "A3" "6543" (i.e. descending). is this possible in vba??

  • Custom lookup using search in VBA

    I am not sure, how to fix this function.

    if Isnumber(search) is true, then get value from lookup_table

    after 2nd round, it ends

    Image of excel data

    Function Slookup(lookup_value As String, lookup_table As Range) As String

Dim i As Integer
On Error GoTo Err
Dim valuetxt As Boolean
Dim cc As Integer

cc = lookup_table.Rows.Count

For i = 1 To cc
    valuetxt = IsError(Application.WorksheetFunction.Search(lookup_table.Cells(i, 1), lookup_value))
    If Not valuetxt Then
        Slookup = lookup_table.Cells(i, 2)
        Exit Function
    End If
    


Slookup = ExcelError.ExcelErrorNA

Err:

Next i

End Function
  • sort two dim array which is declared as one dim array and inserted values as array()

    I'm on dead end which I am no able to figure out even google up :(

    Let's say I have this exemple (please do not comment that it might be better ways to create such an array, this is on purpose):

    Dim someArray() As Variant: ReDim someArray(0 To 0)
ReDim Preserve someArray(0 To UBound(someArray) + 1): someArray(UBound(someArray)) = Array(6, CStr("text1"), CLng(5), CDbl(100))
ReDim Preserve someArray(0 To UBound(someArray) + 1): someArray(UBound(someArray)) = Array(6, CStr("text2"), CLng(3), CDbl(101))
ReDim Preserve someArray(0 To UBound(someArray) + 1): someArray(UBound(someArray)) = Array(6, CStr("text4"), CLng(2), CDbl(102))
ReDim Preserve someArray(0 To UBound(someArray) + 1): someArray(UBound(someArray)) = Array(6, CStr("text3"), CLng(1), CDbl(100))

    and what I need is to fugure out the function to sort by two columns in the someArray() from (1 to UBound(someArray)) based on two colums I pass as arguments:

    • so eather first sort by second column (1) which is CStr, and if records are the same then sort by third column (2)
    • or which two columns I will set as arguments in the function to sort out

    Unfortunately I am very lost here... only option which is realy terrible is to insert data into new sheet, let worksheet function to sort it accordingly, and reinsert into array, which is something i definitely do not wish to do :(

    thank you for ideas...