How can I help my players not to feel useless in situations their characters are not optimally designed for?

Social situations for barbarians:

The party has to negotiate with a tribal leader. He basically has no respect for anyone who doesn't come from a similar background. Either he insists on dealing mainly with the barbarian, or at the very least, will speak to the bard after his "Champion" beats the leader in wrestling or practice-sword combat.

The party has to negotiate with a high-born countess who treats the bard as someone who's simply blocking her view of the muscular, shirtless fellow who's so different from the skinny fops who usually surround her (like your bard). Maybe all the barbarian needs to do is tell some stories of his exploits while she sits on his lap...

There's two parts to this. What the players want and what they're able to achieve. You probably need to work on both halves in turn.

Do the players WANT to participate in the areas they're not optimised for? If not, you need to deal with that first. If player #1 wants 100% social and player #2 wants 100% combat, they may just never enjoy the same game.

But if they'd like to, what can you do? One thing is to try to include things any player can contribute to, regardless of their stats. e.g. make it tactically relevant WHICH orc gets crossbow'd, encourage barbarian to play up their barbarian in conversation even if it's just humour rather than plot. After all what the bard is doing is a lot because they want to rather than because the party need to.

Ideally, have a mix of both. Investigate a thieves guild, which involves a lot of fast talking but might turn to fighting at any moment. Include prisoners, surviving enemies, etc, in combat the bard can talk to.

Even consider, having two things, preferably related, going on at once.

From the other angle, ask if they'd like to be mechanically more relevant, and try to enable that by suggesting actions (or build changes) for them to make, or by including more of a mix in combat: one character frantically sawing at a rope bridge while the other negotiates/fights; or they monsters pushing human slaves into battle the bard might be able to talk to; etc.

The first thing that came to mind was a combined encounter event. Think Scottish Highlands. Something like where caber tossing and other strength-based events must be combined with witty repartee and/or personal theme music which is played whenever the contestant's turn is up.

Somehow I suddenly thought of Kronk:

Kronk's theme song

Spotlight moments =/= spotlight sessions.
It sounds like the bard spends hours (in real time) interacting with NPCs in cities while the barbarian spends hours in dungeon crawls. Mixing those two instead of having two separate modes of play would help; cultists could attack the group at night in the inn, or a tribe of goblins could be persuaded to trade the MacGuffin for something. So instead of having 10 "bard rounds" and 10 "barb rounds" you have 1 bard, 1 barb, 4 bard, 2 bard etc; variety is the spice of life.

It might be necessary to tweak the environment; fighting in an extremely civilized/lawful/peaceful city and not being thrown to jail sounds unlikely while doing that somewhere where "might is right" is more plausible.

Gradient between optimally designed for and useless
You could try to design problems that can be solved in multiple ways. You can also make allowances for the type of checks required; there's a variant (Skills with different abilities) that allows you to use strength for intimidation and similar substitutions, assuming they make sense.

On the other hand, this could be almost impossible if the builds don't have a bit of an overlap. In which case perhaps the only solution is to explain that yes, if you build a character that's not only optimized for one situation but also handicapped in every other situation, you'll be bored. Some ways to work toward a solution:

1. the bard could acquire some wands

2. The bard can be allowed to swap some spells.

3. The barbarian could go talk to NPCs in the rougher areas of the city, where things are more likely to be settled with a fight or a display of strength.(Use Strength for intimidation/persuasion rather than charisma, as noted above)

You mentioned (in a comment) that the Bard selected spells that are largely social and the group tends to fight things that are not affected by the spells. You can change up what they encounter! If you can't change them for narrative reasons, then you could substitute stat blocks or immunities! If your Bard can't use his abilities effectively in combat at all, then it's reasonable to think that the player might become bored.

If your Bard actively wants to participate in combat, you proabably can figure out a way to make it fun. But, if the player chose abilities, that are great out of combat, and poor in combat, because he wanted to excel at social encounters only, then it might be the case that the Bard and the Barbarian are simply not interested in playing the same kind of game. SIMS the tabletop game is not the same as Dungeon Crawler 5.

Point out the Dunning Kruger effect to your Barbarian player. The fact that people with low Inteligence/Social Skills don't have the Inteligence/Social Skills to know they are bad at it. Leading to them in general trying to help even if they make things worse. This will probably make social interactions much more interesting for everyone as they try to fix the Barbarians blunders.

Bards are powerful spell casters and inspiration is always nice. There is no reason for them to waste their actions to shoot crossbows.

Barbarians can be good in social encounters. Say the bard fails at persuasion. The barbarian steps up and says "Me tired of talking. You give us now or I smash".

Your players seem to not know that or have given up on that due to previous failures. In order to fix that you can just tell them out of character or better in character via NPCs (that's what they are there for).

Put an arena with 1on1 fights in a town so that naturally the barbarian wants to participate, just to get wrecked by a bard with Charm Person making the barbarian dance to the crowd's amusement (ideally pick spells that your bard actually has).

Have the person in need of intimidation say to the bard "I'm not afraid of you! What are you gonna do? Sing at me?". If that is not enough hinting you could demonstrate it with a frail person getting backed up by some muscle.

Additionally you could mix it up a little. Combat encounters in towns make them worth paying attention to for the barbarian. Giving enemies in the dungeon enough character to be negotiated with might enable the bard.

First of all, I don't think the GM should be responsible for making sure the players get things to do that they want to do (or "can" do if we're talking build). You might want to let their creativity flow and allow them to create situations or even narrative elements.

Then, use less rolls, and when rolling make sure every outcome is interesting. No roll should ever end up with "nothing happens". The bottomline here is that it's probably more interesting to embrace failure as a fact of life (including adventurers' life) than to try to avoid it at all cost. If RPGs are about "playing to see what's going to happen", failure is as valid an option as anything else. In combat, let them use the environment in addition to their abilities. Don't have them ask but let them assume there is a big rock they can push on the baddies on this ledge they might reach with whatever climbing skill they have.

You might want to dip your toes into "Powered by the Apocalypse" games to get used to this (if you want a D&D-like setting, Dungeon World comes to mind).

Have fun!

Low stats do not mean low participation.

Just because a character does not have high numbers in a particular stat does not mean they cannot participate in any particular encounter. As someone who's played a 5e bard, the fact that you and your player think they're worthless in combat is baffling to me. Why are they only shooting a crossbow when they're arguably one of the most versatile casters in the game?

Likewise, just because your barbarian has low CHA and INT doesn't mean they can't interact with NPCs. I've seen (and played) characters who were obnoxious, dumb oafs that nevertheless manage to engage in entertaining social interactions. A low CHA does not mean that you're shy--it just means you might be bad at lying, for example.

Ultimately, a character is more than their character sheet--they (ideally) have personalities, motivations, and preferences beyond their specific stats. In fact, it's sometimes more fun to have characters that are really bad at things they like to do, like a talkative CHA 6 character.

Explain this to your players.

Your players need to understand that they do not need to be slotted into specific gameplay niches. Most social interactions, for instance, don't actually require rolling checks--your barbarian can still talk to bartenders and ask for information. Likewise, almost every class has something useful to do in combat! Because you say that your players are willing to play both combat and RP, you should emphasize to your players that their gameplay is not fully determined by their stats. Instead of asking themselves, "Who has the best stats to engage in this interaction?" they should be asking, "What would my character be doing in this situation?"

This problem is also partially due to the players' self-imposed limitations. For example, if the bard doesn't have damaging spells, then they're throwing that part of their class away. How would you react to a fighter that refused to use a weapon? Hence, you need your players to accept that they should build characters that are useful in your campaign. When I played a bard, the only damaging spells I really used were Vicious Mockery, Dissonant Whispers, and Fireball--the rest were utility spells, and it worked well.

Create situations where character abilities are relevant.

Once you have buy-in from your players, you should build scenarios and encounters where players' abilities are relevant.

For social encounters, you can build in checks that are not CHA based. PHB 175 describes this variant:

Normally, your proficiency in a skill applies only to a specific kind of ability check... In some situations, though, your proficiency might reasonably apply to a different kind of check.

Therefore, your barbarian might be able to roll a Strength(intimidation) check to intimidate an NPC with a feat of strength, or a Constitution(performance) check to win a drinking game.

On the combat side, you can introduce intelligent enemies that will negotiate with the PCs during a combat. I've played and run a number of encounters that were half combat and half conversation--the skill monkey could play tricks that cause goblins to run away in fear, for example.

You'll have to be careful not to overuse these "crossover" tricks, lest you devalue the other side, but they're a useful tool to allow talkers to participate in combat and combatants to participate in talking.