Data Validations without a Dropdown

[ShawnExcel]

So this is a tough one - I would be amazed if anyone can come up with an answer - I have been struggling with this one for weeks.

Right now I make a survey that folks go out to assess a specific building and they answer questions within Excel. Currently Column A is questions, and Column B consists of dropdowns they can select, which reference a 'database' of answers housed in Sheet2.

Selecting the little dropdown button on a tablet for each of the 100+ questions is annoying and arduous, even making the text smaller and having them zoom in.

The Question: Is there any way to have buttons replace the dropdown. I would like the answers visible when we are on-site asking the questions. I'd like to go from this:



To this:


Or This:


Is there some type of VBA that can pull the data validation info and turn it into radio buttons, and adjust the cell height accordingly (something like that)? If so - that'd be amazing.

 

[Deepak]

Hi,

We will loop into the case but here's a query as well as suggestion.

Query:
How Question will get selected by a user as which is lying in col A either by a dropdown or same is available in rows like as A2:A100.

Suggestion:
You would also try google forms & same is highly recommended by me.

 

[Deepak]

Just a addition: If the VBA is supported on tablet same would also done with userform itself.

 

[NARAYANK991]

Hi ,

Can you clarify whether you want Form control option buttons or ActiveX control option buttons ?

ActiveX :

The buttons themselves are smaller in size.

The text font can be increased in size.

Form :

The buttons themselves are bigger in size.

The text font cannot be increased in size.

Apart from this , Form controls are generally recommended because they are less buggy.

I would suggest that Command Buttons are much better because the buttons can be sized appropriately , the font can be any size and colours can be used both for the button and the text.

Narayan

 

[ShawnExcel]

I have minimal experience with ActiveX so I would prefer form.

We have a database of 700+ questions, so creating a user-form manually for each question is not happening. I have been playing with this code a little bit to see if this is possible - does anyone know how to tweak it to fit what I am going for? Column F displays the number of questions, so the Nrcb variable can be set to the number in column F.

Private Sub ToggleButton1_Click()
Dim Nrcb As Variant
Dim i As Integer
Dim c As Range
Dim Name As String

Nrcb = InputBox("how many option buttons to place?")
Name = InputBox("Group name?")
Set c = ActiveCell
  For i = 1 To Nrcb
     
    With ActiveSheet.OLEObjects.Add("Forms.OptionButton.1", Left:=c.Left, _
      Top:=c.Top, Width:=72, Height:=16)
    .Object.Caption = "Beeeeeer"
    .Object.Value = False
    .Object.GroupName = Name
    End With